最近在给大家部署mysql服务,很多指令都忘了,在这里汇总一下。

简易快速安装mysql:

root@localhost [~] yum install mysql-server
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...

开机自动加载:

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  ]

设置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

给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>

创建新用户,记得重载:

mysql> CREATE USER 'ranshy'@'localhost' IDENTIFIED BY '123456';
mysql> CREATE USER 'ranshy'@'192.168.0.9' IDENTIFIED BY '123456';
mysql> CREATE USER 'ranshy'@'%' IDENTIFIED BY '';
mysql> CREATE USER 'ranshy'@'192.168.0.9';
mysql> FLUSH PRIVILEGES;

删除一个用户,同样记得重载:

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;

授权用户数据库的操作权限(读写):

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;

查看授权信息:

mysql> show grants for ranshy ;
+--------------------------------------------------------------------+
| Grants for ranshy@%                                                |
+--------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'ranshy'@'%' IDENTIFIED BY PASSWORD '*501AA' |
+--------------------------------------------------------------------+
1 row in set (0.00 sec)

远程连接mysql,记得iptables打开3306端口。