最近在给大家部署mysql服务,很多指令都忘了,在这里汇总一下。
简易快速安装mysql:
root@localhost [~] yum install mysql-server
root@localhost [~] service mysqld start
root@localhost [~] service mysqld start
初始化mysql配置,全部选择n,root没有密码,最后选择y重载:
root@localhost [~] /usr/bin/mysql_secure_installation
Set root password? [Y/n] n
... skipping.
Reload privilege tables now? [Y/n] y
... Success!
Cleaning up...
Set root password? [Y/n] n
... skipping.
Reload privilege tables now? [Y/n] y
... Success!
Cleaning up...
开机自动加载:
root@localhost [~] chkconfig mysqld on
mysql重启和停止:
root@localhost [~] service mysqld restart
Stopping mysqld: [ OK ]
Starting mysqld: [ OK ]
root@localhost [~] service mysqld stop
Stopping mysqld: [ OK ]
Stopping mysqld: [ OK ]
Starting mysqld: [ OK ]
root@localhost [~] service mysqld stop
Stopping mysqld: [ OK ]
设置mysql默认引擎为InnoDB,需要修改配置文件my.cnf中的 [mysqld] 下面加入default-storage-engine=INNODB 一句,然后重启。
root@localhost [~] vi /etc/my.cnf
********
[mysqld]
default-character-set=utf8
default-storage-engine=INNODB
root@localhost [~] service mysqld restart
********
[mysqld]
default-character-set=utf8
default-storage-engine=INNODB
root@localhost [~] service mysqld restart
给root设置新密码,并连接mysql:
root@localhost [~] /usr/bin/mysqladmin -u root password 'new-password'
root@localhost [~] mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
mysql>
root@localhost [~] mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
mysql>
创建新用户,记得重载:
删除一个用户,同样记得重载:
mysql> SELECT User, Host, Password FROM mysql.user;
+--------+-----------+-------------------------------------------+
| User | Host | Password |
+--------+-----------+-------------------------------------------+
| root | localhost | |
| root | 127.0.0.1 | |
| | localhost | |
| ranshy | % | *501AA83B185BC219F61FC7866B755A56198B78E5 |
+--------+-----------+-------------------------------------------+
mysql> DELETE FROM mysql.user WHERE User='ranshy ';
Query OK, 1 row affected (0.00 sec)
mysql> FLUSH PRIVILEGES;
+--------+-----------+-------------------------------------------+
| User | Host | Password |
+--------+-----------+-------------------------------------------+
| root | localhost | |
| root | 127.0.0.1 | |
| | localhost | |
| ranshy | % | *501AA83B185BC219F61FC7866B755A56198B78E5 |
+--------+-----------+-------------------------------------------+
mysql> DELETE FROM mysql.user WHERE User='ranshy ';
Query OK, 1 row affected (0.00 sec)
mysql> FLUSH PRIVILEGES;
授权用户数据库的操作权限(读写):
mysql> CREATE DATABASE testDB;
Query OK, 1 row affected (0.00 sec)
mysql> GRANT ALL PRIVILEGES ON *.* to 'ranshy'@'%';
mysql> GRANT ALL PRIVILEGES ON testDB.* to 'ranshy'@'%';
mysql> GRANT ALL PRIVILEGES ON testDB.testTB to 'ranshy'@'localhost';
mysql> GRANT SELECT, INSERT, UPDATE, DELETE ON testDB.testTB to 'ranshy'@'localhost';
mysql> FLUSH PRIVILEGES;
Query OK, 1 row affected (0.00 sec)
mysql> GRANT ALL PRIVILEGES ON *.* to 'ranshy'@'%';
mysql> GRANT ALL PRIVILEGES ON testDB.* to 'ranshy'@'%';
mysql> GRANT ALL PRIVILEGES ON testDB.testTB to 'ranshy'@'localhost';
mysql> GRANT SELECT, INSERT, UPDATE, DELETE ON testDB.testTB to 'ranshy'@'localhost';
mysql> FLUSH PRIVILEGES;
查看授权信息:
mysql> show grants for ranshy ;
+--------------------------------------------------------------------+
| Grants for ranshy@% |
+--------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'ranshy'@'%' IDENTIFIED BY PASSWORD '*501AA' |
+--------------------------------------------------------------------+
1 row in set (0.00 sec)
+--------------------------------------------------------------------+
| Grants for ranshy@% |
+--------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'ranshy'@'%' IDENTIFIED BY PASSWORD '*501AA' |
+--------------------------------------------------------------------+
1 row in set (0.00 sec)
远程连接mysql,记得iptables打开3306端口。
Leave a Reply