主从同步可以相当于实时备份，读写分离还能提高数据库的性能，记录一下主从同步的配置

不停机增加从库可以查看 优雅地给正在运行的MySQL添加从库

一.准备

  • 主从数据库版本最好一致
  • 保证数据库的 uuid 不一致
服务器 ip 地址
MySQL Master 192.168.21.53
MySQL Slave 192.168.21.54

二.操作

主数据库操作

开启 binlog

1
2
3
[mysqld]
log_bin=mysql-bin
server-id=1

这里注意 server-id 主从不能一样, 配置完成重启 mysql

创建用于同步的用户账号

登陆数据库

1
mysql -hlocalhost -uroot -ppassword

创建用户并授权

1
CREATE USER 'repl'@'%' IDENTIFIED BY 'A4MyDNdzpHvg5M02KRtm';

授权

1
2
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';
flush privileges;

新开一个窗口查看 master 状态

1
2
3
4
5
6
SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000010 | 747      |              |                  |
+------------------+----------+--------------+------------------+

记录二进制文件名 (mysql-bin.000010) 和位置 (747)

锁表，禁止写入，当前窗口不能退出，这时候开启另一个终端继续操作

1
flush table with read lock;

新开一个窗口查看 master 状态

1
2
3
4
5
6
SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000010 | 747      |              |                  |
+------------------+----------+--------------+------------------+

记录二进制文件名 (mysql-bin.000010) 和位置 (747)

将当前数据导出

1
mysqldump -u root -p --all-databases > /root/backup.sql

取消锁表

1
unlock table;

从服务器操作

导入数据（如果需要的话）

1
mysql -uroot -p < backup.sql

修改配置文件

vim /etc/my.cnf

1
2
[mysqld]
server-id=2

server-id 必须和主数据库不一致，修改完成后重启 mysql

配置从数据库

1
2
CHANGE MASTER TO MASTER_HOST='192.168.21.53', MASTER_USER='repl', MASTER_PASSWORD='A4MyDNdzpHvg5M02KRtm', MASTER_LOG_FILE='mysql-bin.000010', MASTER_LOG_POS=747;
start slave;

查看 slave 状态：

1
show slave status\G;
1
2
3
4
5
6
7
8
9
10
11
12
13
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.21.53
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000010
          Read_Master_Log_Pos: 1055                                           # 表示当前同步到了什么位置
               Relay_Log_File: prod_cukin_mysql_slave-relay-bin.000003
                Relay_Log_Pos: 628
        Relay_Master_Log_File: mysql-bin.000010
             Slave_IO_Running: Yes                                            # 是Yes表示配置成功
            Slave_SQL_Running: Yes                                            # 是Yes表示配置成功

常见问题

uuid 一致问题

很多时候我们是直接克隆的机器做从库，这个时候两台 mysql 机器的 uuid 就是一样的没办法做主从同步，会报错

The slave I/O thread stops because master and slave have equal MySQL server UUIDs; these UUIDs must be different for replication to work

uuid 可以通过以下 sql 来查看

1
show variables like '%server_uuid%';

这个时候需要修改 uuid，规范一点可以使用 uuidgen 来生成一个新的 uuid，配置在 mysql 的数据目录 auto.cnf 文件内

重新同步

如果需要停止同步，比如更换了同步所用到的密码，则需要：

停止 slave 进程, 在 slave 上执行：

1
stop slave;

记录当前的 Read_Master_Log_Pos, 在 slave 上执行：

1
show slave status \G

重新配置同步，在 slave 上执行：

1
2
3
4
5
6
CHANGE MASTER TO 
MASTER_HOST='192.168.21.53', 
MASTER_USER='repl', 
MASTER_PASSWORD='A4MyDNdzpHvg5M02KRtm', 
MASTER_LOG_FILE='mysql-bin.000010', 
MASTER_LOG_POS=747;

开始同步

1
start slave;

禁止从库更新数据

super_read_only 和 read_only 的区别

  1. read_only 参数和 super_read_only 参数默认都是关闭的，read_only 参数设置为 on 的情况下，会组织客户端的更新，但是如果一个账号拥有 super 权限，那么还是可以进行更新的。而 super_read_only 会阻止所有的客户端更新，即使客户端拥有 super 权限也不可以。
  2. 设置 super_read_only 参数为 on 会默认联动的设置 read_only 为 on
  3. 设置 read_only 参数为 off，会默认联动设置 super_read_only 为 off

查看现有的配置

1
show variables like '%read_only%';

设置 readonly

1
set global super_read_only=on;

修改配置文件，永久生效

1
2
[mysqld]
read-only