mysql 导出dmp文件_MySQL高可用-主从复制实践

开篇先介绍场景：

领导：“老杨，可能哪位同事误操作，数据又不同步了，你今天弄一下吧！”

我：“好的，王总，还是11点开始弄吗”

领导：“是的，我已经给运营部和客服部打了招呼，11点让他们做好准备！”

心里万马奔腾，今晚又要加班到凌晨了！

熟悉的场景，你可曾遇到！

今天，我就记录下同步的过程，一主两从的架构

192.168.1.105 主 192.168.1.120 从1 192.168.1.130 从2

MySQL版本都是 5.7.25-0ubuntu0.16.04.2

1. 先配置主库

配置文件：

/etc/mysql/mysql.conf.d/mysqld.cnf

(不同系统，不同版本配置文件目录和名称可能不一致，自行寻找)

去掉下面两行的注释，可以根据喜好修改目录

server-id               = 1  # 每台服务器都不同log_bin                 = /var/log/mysql/mysql-bin.logbinlog_do_db            = shop  #需要同步的db

server_id也可以动态修改

set global server_id=2; 这样不需要重启服务器，但重启后就会失效

2.创建复制专用用户

create user 'rep_user'@'%' identified with mysql_native_password by '123123';

此用户用于主从同步，client连接master。

查看：

mysql> select user,Host from mysql.user ;+------------------+-----------+| user             | Host      |+------------------+-----------+| rep_user         | %         || debian-sys-maint | localhost || mysql.session    | localhost || mysql.sys        | localhost || root             | localhost |+------------------+-----------+5 rows in set (0.00 sec)

3.记录主库复制点

为了防止写操作，需要先锁定表

flush tables with read lock;

然后：

Current database: shop*************************** 1. row ***************************             File: mysql-bin.000008         Position: 514     Binlog_Do_DB: shop Binlog_Ignore_DB: Executed_Gtid_Set: 1 row in set (0.01 sec)

后续就需要从这个位置开始同步了，所以很重要，不能出错！

然后导出数据

mysqldump -hlocalhost -p3306 -uroot -p shop > shop.sql

也可以直接复制MySQL data目录的文件来同步。

4. 同步文件

复制备份文件到从库

scp shop.sql root@192.168.1.120:/data

scp shop.sql root@192.168.1.130:/data

将shop.sql导入到 数据库

需要注意的是，server-uuid不能一样，如果复制文件，需要修改。

5. 配置从库

change master to master_host='192.168.1.105',

master_user='rep_user',master_password='123123', #账号信息

master_log_file='mysql-bin.000008',master_log_pos=154; #master位置信息

change master to记录了要连接主库的信息ip、端口、用户、密码、复制bin文件、偏移量这些信息，这些信息会记录到到文件里，默认在

/var/lib/mysql/master.info里，可以通过变量 master_info_repository设置存放的位置：

mysql> show variables like "%master_info%";+------------------------+-------+| Variable_name          | Value |+------------------------+-------+| master_info_repository | FILE  |

从库的relay信息在/var/lib/mysql/relay-log.info这个文件里。同样可通过relay_log_info_repository来设置

start slave;

启动sql线程和IO线程，并建立到master的连接，

查看主库连接信息

show slave status ;

显示如下：

mysql> show slave statusG;*************************** 1. row ***************************               Slave_IO_State: Waiting for master to send event                  Master_Host: 192.168.1.105                  Master_User: rep_user                  Master_Port: 3306                Connect_Retry: 60              Master_Log_File: mysql-bin.000008          Read_Master_Log_Pos: 785               Relay_Log_File: ubuntu-relay-bin.000002                Relay_Log_Pos: 591        Relay_Master_Log_File: mysql-bin.000008             Slave_IO_Running: Yes            Slave_SQL_Running: Yes              Replicate_Do_DB:           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: 785              Relay_Log_Space: 799              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: 0Master_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: 3fe5881b-6668-11e9-946e-000c29c762e0             Master_Info_File: /var/lib/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: 1 row in set (0.00 sec)

参数说明：

有几个参数比较重要哦：

Master_Log_File 当前读取的master binlog

Read_Master_Log_Pos 当前读取的master binlog位置

Relay_Log_File sql 线程正在读取执行的relay日志文件

Relay_Log_Pos sql 线程正在读取执行的relay日志位置

Slave_IO_Running: Yes IO线程的状态

Slave_SQL_Running: Yes sql线程的状态，这两个额状态必须都是yes ，否则就是出错

Seconds_Behind_Master sql线程比IO线程慢多少，在负载大，内容多的场景，这里可能延迟比较大， 一般 的都是0

6.错误记录

6.1错误1

Last_IO_Errno: 1593

Last_IO_Error: 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.

这是什么错呢？原来在每个server data目录下有一个auto.cnf ,里面的uuid

不能一样，在进行文件copy时容易出现这个问题，修改一下就可以了。

/var/lib/mysql/auto.cnf

[auto]server-uuid=3fe5881b-6668-11e9-946e-000c29c762e0

6.2 错误2

Slave_IO_Running: Connecting

Slave_SQL_Running: Yes

....

Last_IO_Error: Master command COM_REGISTER_SLAVE failed: Access denied for user 'rep_user'@'%' (using password: YES) (Errno: 1045)

根据错误信息应该是权限不对

GRANT REPLICATION SLAVE ON *.* TO 'rep_user'@'%'

flush privileges;

重新配置就可以了

最后，说下这种方式 同步的缺点，就是需要加锁或停服，所以对于不能中断服务的场景就不适用了，那么有没有不停服同步的方法呢，当然是有！后续在详细介绍。