Mysql 5.7主主复制配置
MySQL5.7主主复制配置
主机1IP：192.168.1.2
主机2IP：192.168.1.4
一、首先安装MySQL 5.7
1、卸载两台主机系统中已经有的mysql相关软件包
rpm -qa | grep -i mysql
yum remove mysql(相关软件包名称) -y
2、分别在两台主机系统中执行以下命令
在http://dev.mysql.com/downloads/repo/yum/站点下载mysql57-community-release-el6-8.noarch
rpm -ivh mysql57-community-release-el6-8.noarch.rpm
yum -y install mysql.x86_64 mysql-server.x86_64 mysql-devel.x86_64
3、分别在两台主机上执行以下语句，初始化
mkdir /home/mysql
mysqld --initialize-insecure --user=mysql --datadir=/home/mysql
备注：
如果你用 --initialize 初始化data目录，请用如下命令登录服务
mysql -u root -p
执行上面命令，会提示输入密码，输入随机生成的密码即可。
如果你不知道这个随机密码，请查看error log文件查找这个随机密码。
如果用 --initialize-insecure 初始化data目录，请用root用登录，并不需要输入密码就可以登录，如下命令:
mysql -u root --skip-password
Mysql5.7忘记root密码及mysql5.7修改root密码的方法的相关资料
a、关闭正在运行的 MySQL ：service mysql stop
b、运行:mysqld_safe --skip-grant-tables &
c、为了安全可以这样禁止远程连接：mysqld_safe --skip-grant-tables --skip-networking &
d、使用mysql连接server：mysql -p
e、更改密码：
mysql> update mysql.user set authentication_string=password('wclwcw') where user='root' and Host = 'localhost';
特别提醒注意的一点是，新版的mysql数据库下的user表中已经没有Password字段了
而是将加密后的用户密码存储于authentication_string字段
4、分别登录修改主机1、主机2的mysql密码
输入mysql回车，进入mysql环境
set password = password ('wclwcw');
flush privileges;
5、在主机1(192.168.1.2)的/etc/my.cnf中配置以下内容
[mysqld]
datadir=/home/mysql
socket=/var/lib/mysql/mysql.sock
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
server-id = 1
auto-increment-increment = 2
auto-increment-offset = 1
explicit_defaults_for_timestamp = 1
character_set_server=utf8
interactive_timeout = 57600
log-bin = mysql-bin
expire-logs-days = 100
replicate-do-db = wang #需要同步的数据库
binlog-ignore-db = mysql
binlog-ignore-db = information_schema
slave-skip-errors=all
log-slave-updates
symbolic-links=0
skip-name-resolve
6、在主机1(192.168.1.4)的/etc/my.cnf中配置以下内容
[mysqld]
datadir=/home/mysql
socket=/var/lib/mysql/mysql.sock
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
server-id = 2
auto-increment-increment = 2
auto-increment-offset = 2
character_set_server=utf8
interactive_timeout = 57600
log-bin = mysql-bin
expire-logs-days = 100
replicate-do-db = wang #需要同步的数据库
binlog-ignore-db = mysql
binlog-ignore-db = information_schema
slave-skip-errors=all
log-slave-updates
symbolic-links=0
skip-name-resolve
备注：主机1和主机2都只有server-id不同和 auto-increment- offset不同
auto-increment-offset是用来设定数据库中自动增长的起点的，回为这两能
服务器都设定了一次自动增长值2，所以它们的起点必须得不同，这样才能避
免两台服务器数据同步时出现主键冲突replicate-do-db 指定同步的数据库，
我们只在两台服务器间同步wang数据库
另：auto-increment-increment的值应设为整个结构中服务器的总数，本案例用到两台服务器，所以值设为2
7、分别重启主机1，主机2的mysql服务,使配置生效
service mysqld restart
8、在主机1（192.168.1.2）上创建数据库和表并插入数据
mysql -uroot -pwclwcw
create database wang;
use wang;
create table wclwcw(id int,name varchar(100));
insert into wclwcw value (1,'tom')
9、相互授权
在主机1（192.168.1.2）上
GRANT REPLICATION SLAVE ON *.* TO 'wang'@'192.168.1.4' IDENTIFIED BY 'wclwcw'
flush privileges;
在主机2（192.168.1.4）上
GRANT REPLICATION SLAVE ON *.* TO 'wang'@'192.168.1.2' IDENTIFIED BY 'wclwcw'
flush privileges;
10、互告bin-log信息
a、在主机1（192.168.1.2）上
mysql> show master status;
+------------------+----------+--------------+--------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+--------------------------+
| mysql-bin.000006 | 106 | | mysql,information_schema |
+------------------+----------+--------------+--------------------------+
b、在主机2（192.168.1.4）上
mysql> show master status;
+------------------+----------+--------------+--------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+--------------------------+
| mysql-bin.000008 | 192 | | mysql,information_schema |
+------------------+----------+--------------+--------------------------+
c、在主机1（192.168.1.2）上
mysql> change master to master_host='192.168.1.4',master_user='wang',master_password='wclwcw',master_log_file='mysql-bin.000008',master_log_pos=194;
d、在主机2（192.168.1.4）上
mysql> change master to master_host='192.168.1.2',master_user='wang',master_password='wclwcw',master_log_file='mysql-bin.000007',master_log_pos=1082;
11、在主机1、主机2两服务器都执行以下命令
mysql> start slave;
12、查看状态
主机1上（192.168.1.2）
mysql> show slave status\G
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.1.4
Master_User: zz
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000002
Read_Master_Log_Pos: 778
Relay_Log_File: template-relay-bin.000002
Relay_Log_Pos: 780
Relay_Master_Log_File: mysql-bin.000002
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB: wang
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 778
Relay_Log_Space: 990
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 2
Master_UUID: e6a7bb8f-4fe6-11e6-abd0-fa163e5cb863
Master_Info_File: /home/mysql/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
在主机2（192.168.1.4）上
mysql> show slave status\G；
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.1.2
Master_User: zz
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000002
Read_Master_Log_Pos: 769
Relay_Log_File: template-relay-bin.000002
Relay_Log_Pos: 484
Relay_Master_Log_File: mysql-bin.000002
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB: wang
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 769
Relay_Log_Space: 694
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1
Master_UUID: 10824007-4fe4-11e6-a4ff-fa163ea94ff8
Master_Info_File: /home/mysql/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
当看到了两个yes，即：
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
说明已经配置成功了，同时查看主机2中是否已经有主机1中的数据库
其他
同步数据
用test做的实验，导出将test.sql文件从1.4服务器拷贝到1.2服务器
备份数据前先锁表，保证数据一致性
mysql> FLUSH TABLES WITH READ LOCK;
# mysqldump -uroot -p123456 test> /tmp/test.sql;
mysql> UNLOCK TABLES;
scp /tmp/test.sql root@192.168.1.2:/tmp
在搭建mysql master-slave复制环境时，一切都正常，但是在slave上执行：show slave status\G; 时，出现下面的状况：
Slave_IO_Running: Connecting
Slave_SQL_Running: Yes
一直无法进行正确的复制。查看错误日志文件显示：Connecting error_code:2003
看来是slave 没有连接上master，在网上搜索一下，网上文章大都只是指出了可能的三种错误：
1. 网络不通
2. 密码不对
3. pos不对
我测试网络是可以ping通的。密码也对，pos也对。
后来想了很久，突然想起来了防火墙好像没有关闭！一检查果然。
所以第四种错误是：
4. 防火墙没有关闭。
相关命令如下：
关闭命令： service iptables stop
永久关闭防火墙：chkconfig iptables off
两个命令同时运行，运行完成后查看防火墙关闭状态
service iptables status
另外顺便也将 selinux关闭掉：
1 vi /etc/selinux/config
2 #SELINUX=enforcing #注释掉
3 #SELINUXTYPE=targeted #注释掉
4 SELINUX=disabled #增加
5 :wq #保存，关闭。
6 shutdown -r now #重启系统
查看SELinux的状态：
getenforce
