Mysql/Mariadb主从复制

概念

什么是·mysql/mariadb主从复制？　　　　

mysql/mariadb主从复制：当master（主）数据库发生变化的时候，变化实时会同步到slave（从）数据库中；
类似于：samba共享文件（c/s）、nfs网络文件共享（c/s），当服务端（server）发生变化时，客户端（client）数据内容会根据服务端进行改变；

好处

• 水平扩展数据库的负载能力，后备数据库，主数据库服务器故障后，可切换到从数据库继续工作；
• 容错、高可用，从数据库可用来做备份、数据统计等工作，这样不影响主数据库的性能；
• 数据分布；
• 数据备份；

实现原理

在master机器上，主从同步事件会被写到特殊的log文件中(binary-log);

主从同步事件有3种形式:statement、row、mixed。

 statement：会将对数据库操作的sql语句写入到binlog中。
 row：会将每一条数据的变化写入到binlog中。
 mixed：statement与row的混合。mysql决定什么时候写statement格式的，什么时候写row格式的binlog。

整体上来说，复制有3个步骤：

• master将改变记录到二进制日志(binary log)中（这些记录叫做二进制日志事件，binary log events)；
• slave将master的binary log events拷贝到它的中继日志(relay log)；
• slave重做中继日志中的事件，将改变反映它自己的数据。

下面这章图可以详细解释其原理：

说的简单一些就是：

当对master数据库不管做了增、删、改还是创建了数据库、表等操作时，slave就会快速的接受这些数据以及对象的操作，从而实现主从数据复制，保证数据的一致性。　　

实战

我记得我学php开发的时候，教员经常说的一句话就是：学习半小时，实战一分钟；
好了，接下来到我们实战的时刻了，认真听讲哟！！！ 

环境介绍

系统环境：系统基本上都差不多，一般多数都是linux平台和windows平台比较多，不管什么样的系统环境对这次实战的操作都影响不大，我在这里使用的是docker虚拟出来的centos操作系统，当然您可以选用ubuntu、redhat以及windows系统，这些都不会影响到大的操作；

我这里使用的系统版本：

[root@master /]# cat /etc/redhat-release
centos linux release 8.0.1905 (core) 

这里会用到两台服务器：其中一台masterip172.18.0.2，另外一个slaveip172.18.0.3
数据库版本：（我这里使用的mariadb，你可以使用mysql数据库）

[root@master /]# mysql --version
mysql  ver 15.1 distrib 10.3.11-mariadb, for linux (x86_64) using readline 5.1

配置master数据库

　　1.更改master配置文件
　　　　找到下面文件：

mysql数据库：/etc/mysql/mysql,conf.d/mysqld.cnf
mariadb数据库：/etc/my.cnf.d/mariadb.cnf

注意：我这里是使用yum进行安装的所以默认配置文件是在/etc下面，建议在修改上面两个文件时要先将配置文件进行备份　

修改以下配置：　 

bind-address=172.18.0.2		\\指定master地址
server-id = 1					\\指定唯一的serverid		部分版本没有需手动写入
log_bin = /var/log/mariadb/mariadb-bin.log				\\开启binlog			部分版本没有需手动写入

注意：log_bin这个字段需根据实际情况来定，需找到数据库的日志文件，默认实在 /var/log

 　　2.重新启动数据库

[root@master my.cnf.d]# systemctl restart mariadb		\\centos7、centos8、ubuntu重新启动方式
[root@master my.cnf.d]# server  mariadb  restart		\\centos6及以下版本使用这个重新启动方式

　　mysql重新启动： 

[root@master my.cnf.d]# systemctl restart mysqld		\\centos7、centos8、ubuntu重新启动方式
[root@master my.cnf.d]# server  mysqld  restart		\\centos6及以下版本使用这个重新启动方式

　　3.初始化数据库

[root@master my.cnf.d]# mysql_secure_installation 

note: running all parts of this script is recommended for all mariadb
      servers in production use!  please read each step carefully!

in order to log into mariadb to secure it, we'll need the current
password for the root user.  if you've just installed mariadb, and
you haven't set the root password yet, the password will be blank,
so you should just press enter here.

enter current password for root (enter for none): 					//这里敲回车
ok, successfully used password, moving on...

setting the root password ensures that nobody can log into the mariadb
root user without the proper authorisation.

set root password? [y/n] y						//这里是设置root密码，可不进行设置
new password: 					//新密码
re-enter new password: 			//旧密码
password updated successfully!
reloading privilege tables..
 ... success!


by default, a mariadb installation has an anonymous user, allowing anyone
to log into mariadb without having to have a user account created for
them.  this is intended only for testing, and to make the installation
go a bit smoother.  you should remove them before moving into a
production environment.

remove anonymous users? [y/n] y
 ... success!

normally, root should only be allowed to connect from 'localhost'.  this
ensures that someone cannot guess at the root password from the network.

disallow root login remotely? [y/n] y
 ... success!

by default, mariadb comes with a database named 'test' that anyone can
access.  this is also intended only for testing, and should be removed
before moving into a production environment.

remove test database and access to it? [y/n] y
 - dropping test database...
 ... success!
 - removing privileges on test database...
 ... success!

reloading the privilege tables will ensure that all changes made so far
will take effect immediately.

reload privilege tables now? [y/n] y
 ... success!

cleaning up...

all done!  if you've completed all of the above steps, your mariadb
installation should now be secure.

thanks for using mariadb!
[root@master my.cnf.d]# 

　　4.创建主从同步的用户 

 [root@master ~]# mysql -u root -p				\\登陆数据库
enter password: 			\\输入root密码
welcome to the mariadb monitor.  commands end with ; or \g.
your mariadb connection id is 18
server version: 10.3.11-mariadb-log mariadb server

copyright (c) 2000, 2018, oracle, mariadb corporation ab and others.

type 'help;' or '\h' for help. type '\c' to clear the current input statement.

mariadb [(none)]> grant replication slave on *.* to 'slave'@'%' identified by 'redhat';		
\\创建用户，并设置相应的权限
	\\此处%表示允许从任何地方（除本地外）使用此账号进行登陆使用，在正式环境建议具体到某台主机ip
query ok, 0 rows affected (0.000 sec)			\\表示sql语句执行成功

　　5.更新slave用户权限 

 mariadb [(none)]> flush privileges;		\\每次修改用户权限，都要使用这个sql语句进行更新
query ok, 0 rows affected (0.000 sec)

　　6.导出数据库中所有数据（此步骤取决于slave的权限）

[root@master ~]# mysqldump -u root -p --all-databases --master-data > mariadb.bat.sql
	--all-databases		\\此参数表示备份所有数据库
	--master-data		\\此参数表示将二进制的信息写入到输出文件中，在这里是写入到备份的sql文件中
enter password:

　　7.查看masterr replication log位置

 mariadb [(none)]> show master status;
+--------------------+----------+--------------+------------------+
| file               | position | binlog_do_db | binlog_ignore_db |
+--------------------+----------+--------------+------------------+
| mariadb-bin.000002 |     1974 |              |                  |
+--------------------+----------+--------------+------------------+
1 row in set (0.000 sec)

配置slave数据库

　　1.更改slave配置文件

　　　文件位置与master位置一致

mysql数据库：/etc/mysql/mysql,conf.d/mysqld.cnf
mariadb数据库：/etc/my.cnf.d/mariadb.cnf

　　　注意：我这里是使用yum进行安装的所以默认配置文件是在/etc下面，建议在修改上面两个文件时要先将配置文件进行备份

　　修改以下配置：

bind-address=172.18.0.3		\\指定master地址
server-id = 2					\\指定唯一的serverid		部分版本没有需手动写入
log_bin = /var/log/mariadb/mariadb-bin.log				\\开启binlog			部分版本没有需手动写入

　　注意：log_bin这个字段需根据实际情况来定，需找到数据库的日志文件，默认实在 /var/log

　　2.重新启动数据库

[root@master my.cnf.d]# systemctl restart mariadb		\\centos7、centos8、ubuntu重新启动方式
[root@master my.cnf.d]# server  mariadb  restart		\\centos6及以下版本使用这个重新启动方式

　　mysql重新启动：

[root@master my.cnf.d]# systemctl restart mysqld		\\centos7、centos8、ubuntu重新启动方式
[root@master my.cnf.d]# server  mysqld  restart		\\centos6及以下版本使用这个重新启动方式

　　3.初始化数据库

[root@master my.cnf.d]# mysql_secure_installation 

note: running all parts of this script is recommended for all mariadb
      servers in production use!  please read each step carefully!

in order to log into mariadb to secure it, we'll need the current
password for the root user.  if you've just installed mariadb, and
you haven't set the root password yet, the password will be blank,
so you should just press enter here.

enter current password for root (enter for none): 					//这里敲回车
ok, successfully used password, moving on...

setting the root password ensures that nobody can log into the mariadb
root user without the proper authorisation.

set root password? [y/n] y						//这里是设置root密码，可不进行设置
new password: 					//新密码
re-enter new password: 			//旧密码
password updated successfully!
reloading privilege tables..
 ... success!


by default, a mariadb installation has an anonymous user, allowing anyone
to log into mariadb without having to have a user account created for
them.  this is intended only for testing, and to make the installation
go a bit smoother.  you should remove them before moving into a
production environment.

remove anonymous users? [y/n] y
 ... success!

normally, root should only be allowed to connect from 'localhost'.  this
ensures that someone cannot guess at the root password from the network.

disallow root login remotely? [y/n] y
 ... success!

by default, mariadb comes with a database named 'test' that anyone can
access.  this is also intended only for testing, and should be removed
before moving into a production environment.

remove test database and access to it? [y/n] y
 - dropping test database...
 ... success!
 - removing privileges on test database...
 ... success!

reloading the privilege tables will ensure that all changes made so far
will take effect immediately.

reload privilege tables now? [y/n] y
 ... success!

cleaning up...

all done!  if you've completed all of the above steps, your mariadb
installation should now be secure.

thanks for using mariadb!
[root@master my.cnf.d]# 

　　4.从master将数据库备份复制到slave服务器

[root@slave my.cnf.d]# scp jia@172.18.0.2:/opt/mariadb.bat.sql /opt/
jia@172.18.0.2's password: 
mariadb.bat.sql                   

　　5.将备份数据恢复到slave数据库

[root@slave my.cnf.d]# mysql -u root -p < /opt/mariadb.bat.sql 
 enter password: 

　　6.使slave与master建立连接

[root@slave my.cnf.d]# mysql -u root -p
enter password: 
error 1045 (28000): access denied for user 'root'@'localhost' (using password: yes)
[root@slave my.cnf.d]# mysql -u root -p
enter password: 
welcome to the mariadb monitor.  commands end with ; or \g.
your mariadb connection id is 22
server version: 10.3.11-mariadb-log mariadb server

copyright (c) 2000, 2018, oracle, mariadb corporation ab and others.

type 'help;' or '\h' for help. type '\c' to clear the current input statement.

mariadb [(none)]> stop slave;
query ok, 0 rows affected, 1 warning (0.000 sec)

mariadb [(none)]> change master to
    -> master_host = '172.18.0.2',		\\指定master数据库地址
    -> master_user = 'slave',			\\指定主从复制用户名
    -> master_password = 'redhat',			\\指定主从复制用户密码
    -> master_log_file = 'mariadb-bin.000002',		\\指定master数据库的binlog文件名
    -> master_log_pos=1974;			
query ok, 0 rows affected (0.290 sec)

mariadb [(none)]> start slave;			\\开启复制功能
query ok, 0 rows affected (0.019 sec)

mariadb [(none)]>

　　注意：lmaster_log_file='mariadb-bin.000002与master_log_pos=1974的值，是从上面的 show master status 得到的。

　　到这里已经可以做到主从复制了下面让我们测试一下吧

验证数据库是否同步

测试方法很简单，只需要在主数据库上面创建数据库或者增加一条记录就可以测试是否主从复制配置成功

mariadb [(none)]> show  databases;
+--------------------+
| database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
+--------------------+
3 rows in set (0.018 sec)

mariadb [(none)]> create database a;			\\在主数据库创建a数据库
query ok, 1 row affected (0.063 sec)
mariadb [(none)]> show databases;
+--------------------+
| database           |
+--------------------+
| a                  |
| information_schema |
| mysql              |
| performance_schema |
+--------------------+
4 rows in set (0.000 sec)

　　下面我们来看看从数据库上面有没有a这个数据库吧

mariadb [(none)]> show databases;
+--------------------+
| database           |
+--------------------+
| a                  |
| information_schema |
| mysql              |
| performance_schema |
+--------------------+
4 rows in set (0.075 sec)

　　我们会发现已经有了a这个数据库