MySQL启动多实例

数据库配置

在mysql主目录下生成数据库文件集

mkdir data_3307
mkdir data_3308
mkdir data_3309

在mysql主目录下配置数据库

./scripts/mysql_install_db --datadir=/home/fanbin/code/mysql/data_3307 --user=mysql
./scripts/mysql_install_db --datadir=/home/fanbin/code/mysql/data_3308 --user=mysql 
./scripts/mysql_install_db --datadir=/home/fanbin/code/mysql/data_3309 --user=mysql 

多实例启动配置文件

[mysqld_multi]
mysqld=/home/fanbin/code/mysql/bin/mysqld
mysqladmin=/home/fanbin/code/mysql/bin/mysqladmin
user=root

[mysqld3307]
socket=/tmp/mysql_3307.sock
port=3307
datadir=/home/fanbin/code/mysql/data_3307/
user=mysql

[mysqld3308]
socket=/tmp/mysql_3308.sock
port=3308
datadir=/home/fanbin/code/mysql/data_3308/
user=mysql

[mysqld3309]
socket=/tmp/mysql_3309.sock
port=3309
datadir=/home/fanbin/code/mysql/data_3309/
user=mysql

启动数据库

在bin目录下启动多实例

./mysqld_multi --defaults-file=/home/fanbin/code/mysql/my_multi.cnf start 3307,3308,3309

其他

设置新密码
mysqladmin -uroot -S /tmp/mysql_3307.sock password 123456

登录数据库
mysql -uroot -S /tmp/mysql_3307.sock -p

确认登录的数据库
show variables like '%datadir%';

查用户
select user,host from mysql.user;

更改全部root用户密码
update user set password=password('123456') where user='root';

更改其中host为%
update user set host = '%' where user = 'root' and host = 'hp';
FLUSH PRIVILEGES;

查权限
mysql> show grants for root;

创建用户
grant select on *.* to fanbin@'%' identified by 'fanbin';

更改新添加用户密码
update user set password=password('123456') where user='fanbin';
FLUSH PRIVILEGES;