Mysql主从同步和mysqldump备份
Mysql主从同步工作原理：
（1）Master服务器将数据的改变记录二进制Binlog日志，当Master上的数据发生改变时，则将其改变写入二进制日志中；
（2）Slave服务器会在一定时间间隔内对Master二进制日志进行探测其是否发生改变，如果发生改变，则开始一个I/OThread请求Master二进制事件；
（3）同时主节点为每个I/O线程启动一个Dump线程，用于向其发送二进制事件，并保存至从节点本地的中继日志中，从节点将启动SQL线程从中继日志中读取二进制日志，在本地重放，使得其数据和主节点的保持一致，最后I/OThread和SQLThread将进入睡眠状态，等待下一次被唤醒。
服务器环境：
Master：172.20.26.167 (CentOS7.6)
Slave：172.20.26.198 (CentOS7.6)
不建议跨版本同步，因为版本不同，表结构可能不同，否则同步会有问题。
mysql 的 yum安装方式默认路径如下：
mariadb 数据目录：/var/lib/mysql
mariadb 命令目录：/usr/bin
mariadb 默认配置文件：/etc/my.cnf
mariadb 日志文件：/var/log/mariadb
步骤如下：
一、在master 172.20.26.167服务器上
1、安装mysql
yum install mariadb mariadb-server -y
2、修改my.cnf文件，添加log-bin=master167-bin、server-id=167
[root@v1 ~]# vim /etc/my.cnf
保存退出
3、启动mysql 服务，并登录mysql
[root@v1 ~]# systemctl start mariadb.service
[root@v1 ~]# mysql
4、接下来进行验证二进制功能开启是否成功
方法1、查看主库状态
MariaDB [(none)]> show master status;
方法2、查询主库日志文件
MariaDB [(none)]> show master logs;
方法3、查询二进制变量
MariaDB [(none)]> show variables like "%log_bin";
5、接下来进行授权，指定哪些机器可以连接这台主库服务器
MariaDB [(none)]> grant replication slave on *.* to "tongbu"@"172.20.26.%" identified by "123456";
这里允许172.20.26网段的服务器都可以连接，也可以单独指定某台机器，例如："tongbu"@"172.20.26.198"，或者允许所有机器连接，"tongbu"@"%"
二、接着部署从库slave服务器
1、安装mysql
yum install mariadb mariadb-server -y
2、修改my.cnf文件，添加server-id
vim /etc/my.cnf
server-id=198 （唯一ID号）
保存退出，
3、启动mysql服务，进入数据库，连接主库，启动从库
[root@localhost ~]# vim /etc/my.cnf
[root@localhost ~]# systemctl start mariadb
4、登录mysql，连接主库
MariaDB [(none)]> change master to
-> master_host="172.20.26.167",
-> master_user="tongbu",
-> master_password="123456",
-> master_log_file="mysql167-bin.000003",
-> master_log_pos=245;
MariaDB [(none)]> slave start; #启动slave从库
MariaDB [(none)]> show slave statusG
查看master主库数据库数量及名称
查看slave从库数据库数量及名称，与主库一致
在master主库中创建wordpress数据库
MariaDB [(none)]> create database wordpress charset utf8;
自动将wordpress数据库同步到slave从库中
查看二进制日志文件，可以查看到运行过的数据库命令
[root@v1 ~]# mysqlbinlog /var/lib/mysql/mysql-bin.000003
例如：create database wordpress charser utf8
基本的主从库同步完成。
三、如果忽略某些数据库不同步，或者只同步某些数据库，均需要在从库服务器上配置，但是一般情况下不同时配置，要么只配置忽略的数据库，要么只配置指定的数据库。
1、如果有需要指定忽略某些数据库
[root@bogon ~]# vim /etc/my.cnf
replicate-ignore-db=jf #忽略jf数据库不同步
[root@v1 ~]# systemctl restart mariadb
在master主库上新创建jf数据库
在slave从库上查看数据库，jf数据库并没有同步过来
2、如果有需要指定同步某些数据库：
在slave 服务器上，只同步jfedu、jfe数据库
[root@bogon ~]# vim /etc/my.cnf
replicate-do-db=jfedu #同步jfedu数据库
replicate-do-db=jfe #同步jfe数据库
保存退出，重启mysql 服务
[root@bogon ~]# systemctl start mariadb
在master 上创建jfedu数据库
在slave服务器上查看jfedu数据库是否有同步过来
在master 上创建jfe数据库
在slave服务器上查看jfe数据库是否有同步过来
因上面我们在slave 服务器上设置只同步jfedu、jfe数据库，而没有多指定同步jfed数据库，现在master服务器上创建jfed数据库
在slave服务器上查看jfed数据库是否有同步过来，因没指定同步jfed数据库，所以并没有同步过来
MariaDB [(none)]> show binlog events in "mysql-bin.000004"; #查看二进制日志文件
四、mysqldump 备份
要确保mysql 服务处于启动状态，mysqldump才成功执行。
1、备份mysql这个数据库与表：
[root@v1 ~]# mysqldump -uroot -p --databases mysql > mysql.sql
Enter password:
[root@v1 ~]#
2、备份数据库，并且记录pos点
如果主库有数据，半路中途要加从库，那么使用change master to的时候，这个pos点无法确定，因为主库的pos点一直在变化，而且也很难找到业务的起始点，所以只能通过mysqldump将主库当前数据库里面的所有数据全部导出来做个标记，然后查看二进制文件以及pos点信息
[root@v1 ~]# mysqldump -uroot -p -A --master-data > all.sql
Enter password:
[root@v1 ~]#
[root@v1 ~]# less all.sql #查看导出的数据库截止到的文件以及pos点信息
再把导出来的数据传给slave 服务器
[root@v1 ~]# scp all.sql 172.20.26.198:/root/
[email protected]'s password:
在slave服务器上，停止slave服务，source导入从主库导出是数据库备份文件
MariaDB [(none)]> slave stop;
Query OK, 0 rows affected (0.01 sec)
MariaDB [(none)]> source /root/all.sql
导入完成之后，change master to 连接主库，因刚才查询到的pos点为1615
MariaDB [test]> change master to master_host="172.20.26.167", master_user="tongbu", master_password="123456", master_log_file="mysql-bin.000004", master_log_pos=1615;
MariaDB [test]> slave start; #启动slave 服务
MariaDB [test]> show slave statusG #查看slave 状态，连接正常
成功添加从库，主从库数据一致。
问题排查例子（Slave_SQL_Running: No）：
因在master 上删除了jfedu数据库(drop database jfedu;)，在slave上show slave statusG 查看slave的状态时发现Slave_SQL_Running: No，
处理方法：
- 在master服务器上设置set sql_log_bin=0; 然后删除要删除的数据库，再设置set sql_log_bin=1;
- 查看master 的pos点，show master status;
- 这时需要在slave上执行salve stop，根据master 的pos点重新连接主库change master to
- 成功运行change master to后，再执行show slave statusG 查看slave的状态，
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
- 再在master创建新的数据库，就能同步到slave 上了。