MySQL多实例主从的搭建

MySQL多实例
mysql-5.7.20
解压二进制包
yum -y install perl-JSON
tar xvf mysql-5.7.20-linux-glibc2.12-x86_64.tar.gz
本人喜欢放到/usr/local/下，故
mv mysql-5.7.20-linux-glibc2.12-x86_64 /usr/local/mysql
调整PATH变量
echo "export PATH=/usr/local/mysql/bin:$PATH" >> /etc/profile
source /etc/profile ###重新读取该配置文件

接下来就是修改MySQL关键的配置文件vim /etc/my.cnf

[mysqld_multi]					##启动多实例
mysqld = /usr/local/mysql/bin/mysqld_safe				##指定进程文件路径
mysqladmin = /usr/local/mysql/bin/mysqladmin			##指定管理命令路径
user = root			##指定进程用户

[mysqld1]			##实例进程名称
port=3307			##端口号
datadir=/data3307				##数据库目录，要手动创建
socket=/data3307/mysqld.sock		##指定sock文件的路径和名称
pid-file=/data3307/mysql1.pid		##进程pid号文件位置
log-error=/data3307/mysql1.err		##错误日志位置

[mysqld2]
port=3308
datadir=/data3308
socket=/data3308/mysqld.sock
pid-file=/data3308/mysql2.pid
log-error=/data3308/mysql2.err

每个实例都要有独立的数据库目录、监听端口号、实例名称和独立的sock文件！！！

创建数据库目录
mkdir –p /data{3307,3308}

创建进程运行的所有者和组mysql

useradd mysql
chown –R mysql:mysql /data3307
chown –R mysql.mysql /data3308

注意，这里重点是初始化数据库的命令，跟5.6以前的版本不一样
/usr/local/mysql/bin/mysqld --user=mysql --basedir=/usr/local/mysql --datadir=/data3307 --initialize
2019-03-24T06:59:37.135009Z 1 [Note] A temporary password is generated for root@localhost: M<gS_9t=K-/7
##初始化数据库后会直接在屏幕回显root的初始化密码！！！

/usr/local/mysql/bin/mysqld --user=mysql --basedir=/usr/local/mysql --datadir=/data3308 --initialize
2019-03-24T06:58:48.145556Z 1 [Note] A temporary password is generated for root@localhost: Yb#gI.p=N8<y

接下来是启动多实例了，启动命令也跟5.6之前的都不一样
/usr/local/mysql/bin/mysqld_multi start 1 ##1为实例编号
/usr/local/mysql/bin/mysqld_multi start 2

通过上面回显密码登入数据库
mysql -u root -p'Yb#gI.p=N8<y' -S /data3307/mysqld.sock
mysql -u root -p'M<gS_9t=K-/7' -S /data3308/mysqld.sock
alter user root@"localhost" identified by '123456'; ##修改密码

停止实例的命令
/usr/local/mysql/bin/mysqld_multi -user=root --password=123456 stop 1
/usr/local/mysql/bin/mysqld_multi -user=root --password=123456 stop 2
主从配置
在上的基础上对/etc/my.cnf配置文件进行添加

	[mysqld1]
	……..
	log-bin=mysql-bin
	server-id=11
	[mysqld2]
	…….
	log-bin=mysql-bin
	server-id=12

开启数据库

	/usr/local/mysql/bin/mysqld_multi start 1
	/usr/local/mysql/bin/mysqld_multi start 2

在主库添加授权用户
grant replication slave on *.* to 'hihi'@'%' identified by '123456';

本人是3307做主库

	mysql> show master status\G;
*************************** 1. row ***************************
             File: mysql-bin.000001
         		Position: 437

查看了3307主库的偏移量后登入3308数据库
mysql -u root -p'123456' -S /data3308/mysqld.sock

mysql> change master to master_host='172.18.13.54',
    -> master_port='3307',
    -> master_user='hihi',
    -> master_password='1234567',
    -> master_log_file='mysql-bin.000001',
-> master_log_pos=437;

查看slave的io跟sql线程是否标记yes

mysql> show slave status\G;

*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: localhost
Master_User: hihi
Master_Port: 3307
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 599
Relay_Log_File: localhost-relay-bin.000002
Relay_Log_Pos: 482
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes

mysql-5.6
vim /data3307/my.cnf ##跟5.7不同的是要在实例目录下有my.cnf文件
配置如下：

[client]
port = 3307
socket = /data3307/mysql.sock

[mysqld]
port = 3307
socket = /data3307/mysql.sock
pid-file = /data3307/mysql.pid
datadir = /data3307
basedir = /usr/local/mysql
log-error = /data3307/my3307.err

cp /data3307/my.cnf 	/data3308/
sed -i 's/3307/3308/g' /data3308/my.cnf
chown -R mysql.mysql /data3307
chown -R mysql.mysql /data3308

##初始化数据库，指向实例目录里的my.cnf

/usr/local/mysql/scripts/mysql_install_db --basedir=/usr/local/mysql --defaults-file=/data3307/my.cnf			 
/usr/local/mysql/scripts/mysql_install_db --basedir=/usr/local/mysql --defaults-file=/data3307/my.cnf			

##启动实例

/usr/local/mysql/bin/mysqld_safe --defaults-file=/data3307/my.cnf &
/usr/local/mysql/bin/mysqld_safe --defaults-file=/data3308/my.cnf & 

因为5.6版本的是可以直接无密码登入
/usr/local/mysql/bin/mysql -uroot -S /data3307/mysql.sock
接下来操作如上即可

到这里多实例主从就完成了。
另外，说一个需要注意的小细节，就是到添加授权用户时
grant replication slave on *.* to 'hihi'@'%' identified by '123456';
百分号是不包括127.0.0.1的网段
所以在slave端指定主库IP时尽量避免指向IP为127.0.0.1，否则IO线程会报错