背景
同步前准备
主库&&从库备份配置文件
- 主库
  - 迁移数据
    - 1、将主服务器要同步的数据库枷锁，避免同步时数据发生改变
查询数据库中正在执行的语句
修改配置
从库
排错
查询语句
- 知识扩展

背景

服务器操作系统: 4.15.0-106-generic #107-Ubuntu
mysql版本: 5.7.30-0ubuntu0.18.04.1-log
主库 172.12.113.165
从库 172.12.113.193

同步前准备

1、服务器做快照 2、主库&&从库备份数据目录 cd /data && cp -a mysql mysql_$(date +%F-%T) mv mysql_2021-12-06-16\:04\:18/ backup/

主库&&从库备份配置文件
cp -a /etc/mysql /etc/mysql_$(date +%F-%T) 3、通知停库（不停库也可以锁表）

主库

迁移数据

1、将主服务器要同步的数据库枷锁，避免同步时数据发生改变

查询数据库中正在执行的语句

mysql> show processlist;
+----+------+-----------+------+------------+------+-------------------+----------------------------------------------------------------------+
| Id | User | Host      | db   | Command    | Time | State             | Info                                                                 |
+----+------+-----------+------+------------+------+-------------------+----------------------------------------------------------------------+
|  4 | root | localhost | test | Query      |   80 | Sending data      | select count(*) from t t1 join t t2 join t t3 join t t4 where t1.b=0 |
|  5 | root | localhost | test | Query      |   62 | Flushing tables   | flush tables with read lock                                          |
|  6 | root | localhost | test | Field List |   35 | Waiting for table |                                                                      |
|  7 | root | localhost | test | Query      |    0 | NULL              | show processlist                                                     |
+----+------+-----------+------+------------+------+-------------------+----------------------------------------------------------------------+
4 rows in set (0.00 sec)

#关闭所有打开的表并使用全局读锁锁定所有数据库的所有表，不会刷新脏块

mysql>flush tables with read lock;

#查看锁表结果

mysql> show global status like '%open%';
+----------------------------+------------+
| Variable_name              | Value      |
+----------------------------+------------+
| Com_ha_open                | 0          |
| Com_show_open_tables       | 0          |
| Innodb_num_open_files      | 301        |
| Open_files                 | 36         |
| Open_streams               | 0          |
| Open_table_definitions     | 374        | --表结构文件
| Open_tables                | 2000       | -- 表
| Opened_files               | 36543      |
| Opened_table_definitions   | 691        |
| Opened_tables              | 73144      |
| Slave_open_temp_tables     | 0          |
| Table_open_cache_hits      | 1392146280 |
| Table_open_cache_misses    | 73135      |
| Table_open_cache_overflows | 70316      |
+----------------------------+------------+

2、将主服务器数据库中数据导出

#需要同步三个库的数据
mysqldump -uroot -pQ9****I wallet >wallet-20211207.sql;
mysqldump -uroot -pQ9****I wallet_view >wallet_view-20211207.sql;
mysqldump -uroot -pQ9****I mysql >mysql-20211207.sql;

这个命令是导出数据库中所有表结构和数据，如果要导出函数和存储过程的话使用
# mysqldump -R -ndt db -uroot -pxxxx > db.sql

此时主库就可以接收流量，接下来重要的就是记住第七步中输出的binglog和Position点。其实主从复制需要停服的时间也就花在主库备份导出数据的时间上。

3、备份完成后，解锁主服务器数据库

mysql>unlock tables;

4、将主库备份数据传送到从库服务器

$ scp wallet-20211207.sql wallet_view-20211207.sql mysql-20211207.sql root@db193:/data/

修改配置

5、修改MySQL配置

vi /etc/mysql/mysql.conf.d/mysqld.cnf
[mysqld_safe]
socket          = /var/run/mysqld/mysqld.sock
nice            = 0
[mysqld]
log-bin         = mysql-bin         #必须配置，也可以配置路径
server-id       = 165               #必须配置，一般为服务器IP尾号
user            = mysql
pid-file        = /var/run/mysqld/mysqld.pid
socket          = /var/run/mysqld/mysqld.sock
binlog_do_db            = mysql         #需要同步的库，多个库叠加配置，如果不配置，主库更新，配置不会同步数据。
binlog_do_db            = wallet2
binlog_do_db            = wallet2_view
port            = 3306
basedir         = /usr
datadir         = /data/mysql
tmpdir          = /tmp
lc-messages-dir = /usr/share/mysql
skip-external-locking
key_buffer_size         = 16M
max_allowed_packet      = 16M
thread_stack            = 192K
thread_cache_size       = 8
myisam-recover-options  = BACKUP
query_cache_limit       = 1M
query_cache_size        = 16M
log_error = /var/log/mysql/error.log
expire_logs_days        = 10
max_binlog_size   = 100M

6、重启MySQL，创建允许从服务器同步数据的账户

#创建slave账号rep1，密码123456
mysql>GRANT ALL PRIVILEGES ON *.* TO rep1@'%' IDENTIFIED BY '123456'; 
#更新数据库权限
mysql>flush privileges;

7、查看主服务器状态

mysql> show master status\G;
*************************** 1. row ***************************
             File: mysql-bin.000001
         Position: 588
     Binlog_Do_DB:
 Binlog_Ignore_DB:
Executed_Gtid_Set:
1 row in set (0.00 sec)
ERROR:
No query specified
执行完这个步骤后不要再操作主服务器数据库了，防止其状态值发生变化

从库

1、将初始数据导入从服务器数据库

mysql>create database wallet;
mysql>create database wallet_view;
mysql>use wallet;
mysql>source /data/wallet-20211207.sql;
mysql>use wallet_view;
mysql>source /data/wallet_view-20211207.sql;
mysql>source /data/mysql-20211207.sql;

2、修改MySQL配置

vi /etc/mysql/mysql.conf.d/mysqld.cnf
[mysqld]
pid-file        = /var/run/mysqld/mysqld.pid
socket          = /var/run/mysqld/mysqld.sock
datadir         = /var/lib/mysql
log-error       = /var/log/mysql/error.log
log-bin         = mysql-bin        #可选配置
server-id       = 193              #必须配置
# By default we only accept connections from localhost
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

3、执行同步命令

#执行同步命令，设置主服务器ip，同步账号密码，同步位置
mysql>change master to master_host='172.12.113.165',master_user='rep1',master_password='123456',master_log_file='mysql-bin.000001',master_log_pos=588;
#开启同步功能
mysql>start slave;

4、查看从服务器状态

mysql>show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 172.12.113.193
                  Master_User: account
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 588
               Relay_Log_File: db2-relay-bin.000002
                Relay_Log_Pos: 337686
        Relay_Master_Log_File: mysql-bin.000033
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB:
          Replicate_Ignore_DB:
          ...
Slave_IO_Running及Slave_SQL_Running进程必须正常运行，即Yes状态，否则说明同步失败

排错

1、Fatal error: The slave I/O thread stops because master and slave have equal MySQL server

场景一：拷贝主库的数据目录
mysql 5.6的复制引入了uuid的概念，各个复制结构中的server_uuid得保证不一样，但是查看到直接copy data文件夹后server_uuid是相同的，show variables like ‘%server_uuid%’;
解决方法：找到data文件夹下的auto.cnf文件，修改里面的uuid值，保证各个db的uuid不一样，重启db即可

场景二：创建主从关系时copy了同样的my.cnf文件，报错
Fatal error: The slave I/O thread stops because master and slave have equal MySQL server ids;

原因分析：和server_uuid类似，servier_id也得保证不一样
解决方法：找到my.cnf配置文件中的server_id，修改从库的server_id保证和复制结构中的其他db不一样，重启db即可

查询语句

#查询binlog日志
mysql> show binary logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |       611 |
| mysql-bin.000002 |       177 |
| mysql-bin.000003 | 105598546 |
| mysql-bin.000004 | 105505214 |
| mysql-bin.000005 | 105358824 |
| mysql-bin.000108 |  78983938 |
| mysql-bin.000109 |       818 |
+------------------+-----------+
7 rows in set (0.00 sec)
#查询mysql用户及其授权服务器
SELECT DISTINCT CONCAT('User: ''',user,'''@''',host,''';') AS query FROM mysql.user;

知识扩展

flush tables with read lock，关闭所有打开的表，同时对于所有数据库中的表都加一个读锁，直到显示地执行unlock tables，该操作常常用于数据备份的时候。
也就是将所有的脏页都要刷新到磁盘，然后对所有的表加上了读锁，于是这时候直接拷贝数据文件也就是安全的。但是如果你发出命令flush tables with read lock时，还有其他的操作，而起是很耗时的操作呢？
先说写操作，这个FTWRL肯定是得等的，等写操作完成才能执行FTWRL，这个很好理解。
那么对于其他的读操作呢？比如说在FLWRL发出之前有一个query：select count(*) from tb，那么FTWRL也得等待(show processlist可以看到 waiting for table flush)。

flush tables with read lock在测试的时候，它有可能花几毫秒就可以完成，就像我遇到的情况，在生产环境也可能花几个小时才能完成。在此期间，MySQL服务完全block住了，而不仅仅是read-only。因为flush tables with read lock会做一下动作：

请求锁
flush tables with read lock请求全局read lock。当这种情况发生时，其他进程如果有修改动作的话就会被阻塞。从理论上讲，这种情况并不是很糟糕，因为flush tables with read lock只需要read lock，其它命令（只需要read lock的命令）可以和flush tables with read lock并存。然而，事实上，大多数表需要读和写锁的。例如：第一个写语句会被这个全局的读锁阻塞，而子查询又会被第一个写语句阻塞，所以真正有效果的是使用的是排它锁，所有新请求就会被阻塞，包括读查询语句。

等待锁
在flush tables with read lock成功获得锁之前，必须等待所有语句执行完成（包括SELECT）。所以如果有个慢查询在执行，或者一个打开的事务，或者其他进程拿着表锁，flush tables with read lock就会被阻塞，直到所有的锁被释放。请看下面的例子：
可以看到线程6没有连进来，因为MySQL的客户端连接时没有指定-A，它尝试获取当前库下的所有的表和列。线程5也没有flush tables，因为它在等线程4释放锁。

刷新表
当flush tables with read lock拿到锁后，必定flush data。对于MyISAM引擎，不光是刷新它自己的data，也刷新操作系统的data到disk上（MyISAM relies on the filesystem block cache for caching reads to the data rows and indexes, while InnoDB does this within the engine itself, combining the row caches with the index caches），所以如果是MyISAM表的话有可能会花费很长时间。

持有锁
我们可以使用unlock tables或者其它命令来释放锁。

结论
一个备份系统一般都是在生产环境中用的，所以我们不能简单的认为flush tables with read lock很快就执行完。在某些情况下，执行慢是没法避免的。但是我们可以配置备份系统避免这种global lock。

mysql 主从复制

背景