mysql主从复制的坑

主从mariadb搭建

环境：
system:centos7.6.1810
PHP:7.2.19
nginx:1.12.2
mysql:MariaDB-5.5.60
rabbitmq:4.3
服务器：
master：192.168.0.103
slave：192.168.0.104 192.168.0.105

操作:
一.主服务器配置

1.编辑192.168.0.103的/etc/my.cnf
[root@localhost ~]# cat /etc/my.cnf
----------------
# 配置主从时需要添加以下信息 start
innodb_file_per_table=NO
log-bin=/var/lib/mysql/master-bin #log-bin没指定存储目录，则是默认datadir指向的目录
binlog_format=mixed
server-id=103 #每个服务器都需要添加server_id配置，各个服务器的server_id需要保证唯一性，实践中通常设置为服务器IP地址的最后一位
#配置主从时需要添加以下信息 end 
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
----------------

编辑完后，保存并退出:wq!

2.重启mariadb服务，输入命令 
[root@localhost ~]# systemctl restart mariadb

3.登录mariadb 
[root@localhost ~]# mysql -u root -p12345678 
注：-p后是密码，中间没有空格 

4.创建帐号并赋予replication的权限 
从库,从主库复制数据时需要使用这个帐号进行
MariaDB [(none)]> GRANT REPLICATION SLAVE ON *.* TO 'root'@'192.168.0.%' IDENTIFIED BY '12345678';
Query OK, 0 rows affected (0.00 sec)

5.备份数据库数据，用于导入到从数据库中

## 加锁
实际工作中，备份的时候是不让往库中写数据的，所以数据库要加锁，只能读
MariaDB [(none)]> FLUSH TABLES WITH READ LOCK;
Query OK, 0 rows affected (0.00 sec)

## 记录主库log文件及其当前位置
MariaDB [(none)]> SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 |      550 |              |                  |
+------------------+----------+--------------+------------------+
记住File和Position的部分，从服务器会用到

##  备份数据，输入命令：
[root@localhost ~]# mysqldump -uroot -p --all-databases > /root/db.sql
注:退出mariadb,在服务器内输入以上命令

##  拷贝到从库对应目录
[root@localhost ~]# scp db.sql root@192.168.0.104:/root/db.sql 
The authenticity of host '192.168.0.104 (192.168.0.104)' can't be established.
ECDSA key fingerprint is SHA256:8BmkKDkAVjGwskrNAZe/faoG8z2DCP7KTuvJQZ2DRGQ.
ECDSA key fingerprint is MD5:20:ef:8b:8e:6b:fc:0f:46:4a:a5:9d:14:0f:dd:cb:67.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added '192.168.0.104' (ECDSA) to the list of known hosts.
root@192.168.0.104's password: 
db.sql                                 
注：可以去从库/root下查看db.sql
或
者直接从主库导入从库
直接在从库导主库的数据库，命令如下:
mysqldump -h 192.168.117.xx -uroot -pxxxxxx --all-databases > /root/db.sql
或
写shell免密脚本
___________________________
1.因为脚本中用到SCP命令，需要先实现scp不用输入密码（通过证书实现）。 
在数据库服务器执行：

ssh-keygen

按四五次回车证书就生成了，将id_rsa.pub拷贝到另外一台文件服务器的/root/.ssh目录并命名为authorized_keys

2.在数据库服务器编写shell脚本如下：
#!bin/bash
cd /home
a=`date +%Y%m%d-%H%M%S`
mysqldump -u数据用户名 -p数据库密码 --all-databases > backup-$a.sql
scp -P 80 backup-$a.sql 服务器用户名@服务器地址:文件目录
rm -rf backup-$a.sql
____________________________


##  解锁 主库
数据备份完成后，就可以释放主库上的锁：
MariaDB [(none)]> UNLOCK TABLES;
Query OK, 0 rows affected (0.00 sec)

二.从服务器配置

以下在从服务器上的操作

1.导入主库的数据
[root@localhost ~]# mysql -uroot -p < db.sql

2.从服务器/etc/my.cnf配置,设置relay-log 
my.cnf文件中添加一行relay_log=relay-bin 
如果不设置，默认是按主机名 + “-relay-bin”生成relay log
-------------------
[root@localhost ~]# cat /etc/my.cnf
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

`#配置主从时需要添加以下信息 start
innodb_file_per_table=NO
server-id=201 #一般与服务器ip的最后数字一致
relay-log=/var/lib/mysql/relay-bin
#配置主从时需要添加以下信息 end 
`
# Settings user and group are ignored when systemd is used.
# If you need to run mysqld under a different user or group,
# customize your systemd unit file for mariadb according to the
# instructions in http://fedoraproject.org/wiki/Systemd

[mysqld_safe]
log-error=/var/log/mariadb/mariadb.log
pid-file=/var/run/mariadb/mariadb.pid

#
# include all files from the config directory
#
!includedir /etc/my.cnf.d
---------------------

3.重启服务
[root@localhost ~]# systemctl restart mariadb

4.登录mariadb
[root@localhost ~]# mysql -uroot -p12345678

5.设置主从复制
CHANGE MASTER TO MASTER_HOST='192.168.0.104',MASTER_USER='root', MASTER_PASSWORD='12345678', MASTER_LOG_FILE='master-bin.000001', MASTER_LOG_POS= 550

这个命令完成以下几个任务：
    a.设置当前服务器为主服务器(192.168.0.103)的从库
    b.提供当前数据库(从库)从主库复制数据时所需的用户名和密码，即上面的GRANT REPLICATION SLAVE ON *.* TO 'root'@'192.168.0.%' IDENTIFIED BY '12345678';设置的
    c.指定从库开始复制主库时需要使用的日志文件和文件位置，即上面主库执行SHOW MASTER STATUS;显示结果中的File和Position

6.开启主从复制
MariaDB [(none)]> START SLAVE;
Query OK, 0 rows affected (0.00 sec)

7.查看从库状态
MariaDB [(none)]> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 10.69.5.200
                  Master_User: root
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: master-bin.000001
          Read_Master_Log_Pos: 694
               Relay_Log_File: relay-bin.000003
                Relay_Log_Pos: 530
        Relay_Master_Log_File: master-bin.000001
             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: 694
              Relay_Log_Space: 818
              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: 200
1 row in set (0.00 sec)

注意：结果中Slave_IO_Running和Slave_SQL_Running必须为Yes，如果不是，需要根据提示的错误修改。

三.验证

主服务器：
MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |             
| performance_schema |
| test               |
+--------------------+
5 rows in set (0.04 sec)

MariaDB [(none)]> use test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
MariaDB [mytest]> select * from user;
+----+------+
| id | name |
+----+------+
|  1 | t    |
|  2 | t2   |
|  3 | t3   |
+----+------+
3 rows in set (0.00 sec)

MariaDB [mytest]> insert into user(name) values('t4');
Query OK, 1 row affected (0.01 sec)

MariaDB [mytest]> select * from user;
+----+------+
| id | name |
+----+------+
|  1 | t    |
|  2 | t2   |
|  3 | t3   |
|  4 | t4   |
+----+------+
4 rows in set (0.00 sec)


##  查看从服务器数据是否变化：
MariaDB [(none)]> use mytest;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
MariaDB [mytest]> select * from user;
+----+------+
| id | name |
+----+------+
|  1 | t    |
|  2 | t2   |
+----+------+
2 rows in set (0.00 sec)

MariaDB [mytest]> select * from user;
+----+------+
| id | name |
+----+------+
|  1 | t    |
|  2 | t2   |
|  4 | t4   |
+----+------+
3 rows in set (0.00 sec)

可以看到，从服务器更新了数据

四.参考文献
坑与步骤：https://blog.youkuaiyun.com/mingliangniwo/article/details/54606894
错误排查文献：http://www.voidcn.com/article/p-seobnjmg-bao.html