Centos7下mysql5.7.22主从配置
一:服务器配置
1.1修改root密码:
ALTER USER 'root'@'localhost' IDENTIFIED BY 'xxxx';
1.2添加远程登陆用户:
GRANT ALL PRIVILEGES ON *.* TO 'abc'@'%' IDENTIFIED BY 'xxxx' WITH GRANT OPTION;
1.3统一默认编码:
vim /etc/my.cnf 在 [mysqld]下添加编码配置:
[mysqld] character_set_server=utf8 init_connect='SET NAMES utf8'
二:主从配置(主):
2.1vim /etc/my.cnf 在 [mysqld]下添加:
#要给从机同步的库 binlog-do-db=test #不给从机同步的库(多个写多行) binlog-ignore-db=mysql binlog-ignore-db=information_schema binlog-ignore-db=performance_schema binlog-ignore-db=sys #自动清理 7 天前的log文件,可根据需要修改 expire_logs_days=7 # 启用二进制日志 log-bin=mysql-bin # 服务器唯一ID，一般取IP最后一段 server-id=101
2.2 开启log_bin 在 [mysqld]下添加:
log-bin = mysql-bin server-id = 072
重启服务:
systemctl restart mysqld
查看是log_bin否启动:
mysql -u root -p
mysql> show variables like '%log_bin%';
+---------------------------------+---------------------------------------+
| Variable_name | Value |
+---------------------------------+---------------------------------------+
| log_bin | ON |
| log_bin_basename | /usr/local/mysql/data/mysql-bin |
| log_bin_index | /usr/local/mysql/data/mysql-bin.index |
| log_bin_trust_function_creators | OFF |
| log_bin_use_v1_row_events | OFF |
| sql_log_bin | ON |
+---------------------------------+---------------------------------------+
6 rows in set (0.01 sec)
2.3重启服务器
reboot
2.4建立备份账号并授权
mysql>GRANT FILE ON *.* TO 'backup'@'%' IDENTIFIED BY 'xxxx';
mysql>GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* to 'backup'@'%' identified by 'xxxx';
刷新权限列表:
flush privileges;
取消用户授权:
revoke all on *.* from backup;
2.5查询主服务器的状态：
show master status;
mysql> show master status; +------------------+----------+--------------+-------------------------------------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+-------------------------------------------------+-------------------+ | mysql-bin.000002 | 735 | test01 | mysql,information_schema,performance_schema,sys | | +------------------+----------+--------------+-------------------------------------------------+-------------------+ 1 row in set (0.00 sec)
三:主从配置(从):
3.1拷贝主mysql的my.cnf文件到从服务器,替换从my.cnf,并修改如下配置:
scp /etc/my.cnf root@192.168.x.x:/etc/my.cnf
server-id = 2
加上以下参数可以避免更新不及时，SLAVE 重启后导致的主从复制出错。
read_only = 1
master_info_repository=TABLE
relay_log_info_repository=TABLE
3.2配置从mysql:
change master to master_host='x.x.x.x',master_port=3306,master_user='backup',master_password='xxx',master_log_file='mysql-bin.000002',master_log_pos=735;
注!!!语句中间不要断开，master_port为mysql服务器端口号(无引号)，master_user为执行同步操作的数据库账户，“120”无单引号(此处的735就是show master status 中看到的position的值，这里的mysql-bin.000002就是file对应的值)。
如报错:
ERROR 29 (HY000): File '/var/log/mysql-relay-index.index' not found (Errcode: 13 - Permission denied)
编辑my.cnf文件,把log文件集中到/var/log/mysql目录:
[mysqld] relay_log = /var/log/mysql/mysql-relay-bin relay_log_index = /var/log/mysql/mysql-relay-index log_error = /var/log/mysql/mysql-error.err
修改/var/log/mysql目录权限:
chown -R mysql:mysql /var/log/mysql/
重启mysql
systemctl restart mysqld
进入mysql,再次运行change配置
3.3正确执行后启动Slave同步进程
mysql> start slave;
查看从服务器状态
show slave status \G
\G后不加;号
输出:
Slave_IO_Running: No
Slave_SQL_Running: Yes
两个必须为Yes才是正确启动!!!
查看log文件:
vim /var/log/mysql/mysql-error.err
2019-01-16T02:17:02.480802Z 2 [ERROR] Slave I/O for channel '': Fatal error: The slave I/O thread stops because master and slave have equal MySQL server UUIDs; these UUIDs must be different for replication to work. Error_code: 1593
因从虚拟机为主安装mysql后拷贝生成,造成主从mysql UUID值相同,需要修改为不同值:
找到data文件夹下的auto.cnf文件，修改里面的uuid值，保证各个db的uuid不一样，重启db即可
重启mysql
systemctl restart mysqld
再次查看从服务器状态:
mysql> show slave status \G
mymsql> show slave status \G
*************************** 1. row ***************************
Slave_IO_State: Waiting to reconnect after a failed registration on master
Master_Host: 192.168.10.11
Master_User: backup
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000002
Read_Master_Log_Pos: 735
Relay_Log_File: mysql-relay-bin.000003
Relay_Log_Pos: 4
Relay_Master_Log_File: mysql-bin.000002
Slave_IO_Running: Connecting
Slave_SQL_Running: Yes
还是报错:
Slave_IO_Running: Connecting
查看log文件:
2019-01-16T02:53:53.094695Z 7 [ERROR] Slave I/O for channel '': Master command COM_REGISTER_SLAVE failed: Access denied for user 'backup'@'%' (using password: YES) (Errno: 1045), Error_code: 1597
重新赋权:
mysql>GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* to 'backup'@'%' identified by 'xxxx';
切记要刷新:
flush privileges;
mysql> stop slave;
mysql> start slave;
mysql> show slave status \G
mysql> show slave status \G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.100.72
Master_User: wxjcbackup
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000002
Read_Master_Log_Pos: 2708
Relay_Log_File: mysql-relay-bin.000006
Relay_Log_Pos: 320
Relay_Master_Log_File: mysql-bin.000002
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
如果show slave status \G
报错:
Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'Could not find first log file name in binary log index file'
Slave_IO_Running: No Slave_SQL_Running: Yes .... Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'Could not find first log file name in binary log index file'
在主mysql下运行:
mysql> show master status; +------------------+----------+--------------+-------------------------------------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+-------------------------------------------------+-------------------+ | mysql-bin.000004 | 154 | test01 | mysql,information_schema,performance_schema,sys | | +------------------+----------+--------------+-------------------------------------------------+-------------------+ 1 row in set (0.00 sec)
在从mysql下运行:
mysql> show master status; +------------------+----------+--------------+-------------------------------------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+-------------------------------------------------+-------------------+ | mysql-bin.000006 | 154 | test01 | mysql,information_schema,performance_schema,sys | | +------------------+----------+--------------+-------------------------------------------------+-------------------+ 1 row in set (0.00 sec)
两个file编号不一致,以主为准,重新定位:
stop slave;
change master to master_host='x.x.x.x',master_port=3306,master_user='backup',master_password='xxx',master_log_file='mysql-bin.000004',master_log_pos=154;
start slave;
show slave status;
mysql> show slave status \G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.10.11
Master_User: backup
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000004
Read_Master_Log_Pos: 154
Relay_Log_File: mysql-relay-bin.000002
Relay_Log_Pos: 320
Relay_Master_Log_File: mysql-bin.000004
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
四:测试同步:
查看主服务器数据库：
mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | sys | | testdb | +--------------------+ 5 rows in set (0.01 sec)
主库新建数据库：
mysql> create database testdb02; Query OK, 1 row affected (0.00 sec)
从库查看数据库同步情况：
mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | sys | | testdb | | testdb01 | | testdb02 | +--------------------+ 7 rows in set (0.00 sec)
关闭掉主数据库的读取锁定
unlock tables;
show databases;
use test;
show tables;
感谢:
https://blog.csdn.net/happy_pinocchio/article/details/79592422
五：重新做主从，完全同步 (适用于主库从库的数据相差较大；要求数据完全统一的情况 )
5.1 先进入主库，进行锁表，此处锁定为只读状态，防止数据写入
(可选，因如有数据库备份，可直接利用备份)
flush tables with read lock;
5.2 进行数据备份,把数据备份为.sql的文件
(可选，因如有数据库备份，可直接利用备份)
切换到mysql用户
su mysql
mysqldump -uroot -p‘密码’ --all-databases > /data/mysql/backup/mysql.back.sql
如报错：
先查看vim /etc/profile是否添加mysql环境变量，如没有则添加
export PATH=$PATH:/usr/local/mysql/bin
export PATH
5.3 进入主库，进行解锁
(可选，因如有数据库备份，可直接利用备份)
unlock tables;
5.4 把mysql的备份文件传输到从库服务器上
(位置任意，但要能找到)
scp -r /data/mysql/backup/mysql.bask.sql root@mysql02:/data/mysql/backup/
5.5 进入从库，停止从库的状态
stop slave;
清除slave上的同步位置，删除所有旧的同步日志，使用新的日志重新开始.(使用前先停止slave服务）
reset slave;(可选)
5.6 在从库中导入数据备份
source /data/mysql/backup/mysql.back.sql ;
或
mysql -uroot -p‘....’ database -f < /data/mysql/backup/mysql.bask.sql
(-f 为跳过错误的Sql，继续往下执行，可不加)
5.7 重新设置从库同步
change master to master_host = '主库的IP’, master_user = '设置主从时设定的主库的用户', master_port=主库的端口, master_password='主库设定的密码', master_log_file = 'mysqld-bin.xxxxxx', master_log_pos=xxxx;
注意： master_log_file与master_log_pos 是主库show master status信息里的| File与Position
5.8 重新开启从库同步
start slave;
5.9 查看同步状态
mysql> show slave status\G
查看：
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
感谢：
https://blog.csdn.net/GX_1_11_real/article/details/80658527
