Mysql主从同步和mysqldump备份

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,

处理方法:

  1. 在master服务器上设置set sql_log_bin=0; 然后删除要删除的数据库,再设置set sql_log_bin=1;
  2. 查看master 的pos点,show master status;
  3. 这时需要在slave上执行salve stop,根据master 的pos点重新连接主库change master to
  4. 成功运行change master to后,再执行show slave statusG 查看slave的状态,

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

  1. 再在master创建新的数据库,就能同步到slave 上了。