一、MySQL主从原理

MySQL主从又叫Replication、AB复制。简单讲，就是A和B两台机器做主从后，在A上写数据，另一台机子B也会跟着写数据，两者数据实时同步。

MySQL主从是基于binlog的，主上要开启binlog才能进行主从。

主从过程：

1、主将更改操作记录到binlog里

2、从将主的binlog事件（sql语句）同步到从本机上并记录在relaylog里

3、从根据relaylog里面的sql语句按顺序执行

主上有一个log dump线程，用来和从的I/O线程传递binlog。

从上有两个线程，其中I/O线程用来同步主的binlog，并生产relaylog；另外一个SQL线程用来把relaylog里面的sql语句执行。

二、MySQL主从配置

这里使用mariadb数据库演示主从。

主服务器：RHEL 7.5，ip：192.168.10.205，主机名：node0

从服务器：RHEL 7.5，ip：192.168.10.101，主机名：node1

1、主服务器配置

这里使用mariadb演示。

（1）mariadb安装

这里使用最新版的mariadb-10.3.7演示

·下载mariadb二进制包

[root@node0 ~]# curl -O http://mirrors.neusoft.edu.cn/mariadb//mariadb-10.3.7/bintar-linux-glibc_214-x86_64/mariadb-10.3.7-linux-glibc_214-x86_64.tar.gz

·解压mariadb-10.3.7到/usr/local目录中，并做软连接为mysql

[root@node0 ~]# tar xf mariadb-10.3.7-linux-glibc_214-x86_64.tar.gz -C /usr/local/
[root@node0 ~]# cd /usr/local/
[root@node0 local]# ln -sv mariadb-10.3.7-linux-glibc_214-x86_64/  mysql
‘mysql’ -> ‘mariadb-10.3.7-linux-glibc_214-x86_64/’
[root@node0 local]# 

·创建mysql用户
[root@node0 ~]# useradd -r -s /sbin/nologin mysql

·将mysql属主属组改为mysql

[root@node0 local]# chown -R mysql.mysql mysql/

·创建数据数据存放目录：/mydata/mariadb

[root@node0 ~]# mkdir /mydata/mariadb -pv
mkdir: created directory ‘/mydata’
mkdir: created directory ‘/mydata/mariadb’
[root@node0 ~]# 

·数据库初始化安装

[root@node0 local]# cd mysql/
[root@node0 mysql]# ./scripts/mysql_install_db --user=mysql --basedir=/usr/local/mysql --datadir=/mydata/mariadb
Installing MariaDB/MySQL system tables in '/mydata/mariadb' ...
OK

To start mysqld at boot time you have to copy
support-files/mysql.server to the right place for your system

PLEASE REMEMBER TO SET A PASSWORD FOR THE MariaDB root USER !
To do so, start the server, then issue the following commands:

'/usr/local/mysql/bin/mysqladmin' -u root password 'new-password'
'/usr/local/mysql/bin/mysqladmin' -u root -h node0 password 'new-password'

Alternatively you can run:
'/usr/local/mysql/bin/mysql_secure_installation'

which will also give you the option of removing the test
databases and anonymous user created by default.  This is
strongly recommended for production servers.

See the MariaDB Knowledgebase at http://mariadb.com/kb or the
MySQL manual for more instructions.

You can start the MariaDB daemon with:
cd '/usr/local/mysql' ; /usr/local/mysql/bin/mysqld_safe --datadir='/mydata/mariadb'

You can test the MariaDB daemon with mysql-test-run.pl
cd '/usr/local/mysql/mysql-test' ; perl mysql-test-run.pl

Please report any problems at http://mariadb.org/jira

The latest information about MariaDB is available at http://mariadb.org/.
You can find additional information about the MySQL part at:
http://dev.mysql.com
Consider joining MariaDB's strong and vibrant community:
Get Involved


[root@node0 mysql]# 

·mariadb服务文件配置

将support-files目录中的mysql.server文件复制到/etc/rc.d/init.d目录中，并重命名为mariadb。最后添加可执行权限。

[root@node0 mysql]# cd /usr/local/mysql/
[root@node0 mysql]# cp support-files/mysql.server /etc/rc.d/init.d/mariadb
[root@node0 mysql]# chmod +x /etc/rc.d/init.d/mariadb

·将mariadb添加到系统服务管理

[root@node0 mysql]# chkconfig --add mariadb

（2）mariadb主配置

[root@node0 ~]# vim /etc/my.cnf
[mysqld]
datadir=/mydata/mariadb
socket=/tmp/mysql.sock
server-id=205
log-bin=node0
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
# 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

参数解释：

datadir：数据数据目录，这里是：/mydata/mariadb

socket：socket文件，这里是/tmp/mysql.sock

server-id：服务id，自定义，但不能重复。

log-bin：logbin文件名，自定义

启动mariadb：

[root@node0 ~]# systemctl start  mariadb
[root@node0 ~]# 

（3）创建测试数据库

创建一个用于测试的数据库，名字为：test001

[root@node0 ~]# /usr/local/mysql/bin/mysql -uroot -p -e "create database test001"
Enter password: 
[root@node0 ~]# 

创建一个表，名字为：test

[root@node0 ~]# /usr/local/mysql/bin/mysql
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 11
Server version: 10.3.7-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)]> create table test001.test(id int,name varchar(100));
Query OK, 0 rows affected (0.023 sec)

MariaDB [(none)]> 

（4）用于主从的数据库认证用户

MariaDB [(none)]> grant replication slave on *.* to 'repl'@'192.168.10.%'  identified by '123456';
Query OK, 0 rows affected (0.031 sec)

MariaDB [(none)]> flush tables with read lock;
Query OK, 0 rows affected (0.002 sec)

MariaDB [(none)]> show master status;
+--------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+--------------+----------+--------------+------------------+
| node0.000001 | 324 |  |  |
+--------------+----------+--------------+------------------+
1 row in set (0.007 sec)

MariaDB [(none)]> 

请记住： node0.000001 和 324。后面会用到。从连接主的时候，要使用这两个参数。

（5）备份测试用的数据库

[root@node0 ~]# /usr/local/mysql/bin/mysqldump -uroot -p test001 > test001.sql
Enter password: 
[root@node0 ~]# 

2、从服务器配置

mariadb安装跟主服务器安装、初始化一样。安装过程省略。

配置从配置文件：

[root@node1 ~]# vim /etc/my.cnf
[mysqld]
datadir=/mydata/mariadb/
socket=/tmp/mysql.sock
server-id=101
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
# 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@node0 ~]# scp test001.sql 192.168.10.101:/root
root@192.168.10.101’s password:
test001.sql 100% 1851 876.5KB/s 00:00
[root@node0 ~]#

4、从服务器恢复主服务器发来的数据库备份文件

先创建一个test001数据库，再把备份导入该数据库。

[root@node1 ~]# /usr/local/mysql/bin/mysql -uroot -p  -e "create database test001";
Enter password: 
[root@node1 ~]# /usr/local/mysql/bin/mysql -uroot -p test001 < test001.sql 
Enter password: 
[root@node1 ~]# 

5、登录从服务器的数据库

登录数据库执行：stop slave命令：

[root@node1 ~]# /usr/local/mysql/bin/mysql
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 12
Server version: 10.3.7-MariaDB 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)]> 

在slave从服务器上设置将从服务器指向主服务器：

MariaDB [(none)]> change master to master_host='192.168.10.205',master_user='repl',master_password='123456',master_log_file='node0.000001',master_log_pos=324;
Query OK, 0 rows affected (0.312 sec)

启动复制线程：
MariaDB [(none)]> start slave;
Query OK, 0 rows affected (0.005 sec)

MariaDB [(none)]> 

查看一下状态：

   MariaDB [(none)]> show slave status \G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
   Master_Host: 192.168.10.205
   Master_User: repl
   Master_Port: 3306
 Connect_Retry: 60
   Master_Log_File: node0.000001
   Read_Master_Log_Pos: 648
Relay_Log_File: node1-relay-bin.000002
 Relay_Log_Pos: 875
 Relay_Master_Log_File: node0.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: 648
   Relay_Log_Space: 1184
   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: 205
Master_SSL_Crl: 
Master_SSL_Crlpath: 
Using_Gtid: No
   Gtid_IO_Pos: 
   Replicate_Do_Domain_Ids: 
   Replicate_Ignore_Domain_Ids: 
 Parallel_Mode: conservative
 SQL_Delay: 0
   SQL_Remaining_Delay: NULL
   Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
  Slave_DDL_Groups: 1
Slave_Non_Transactional_Groups: 0
Slave_Transactional_Groups: 1
1 row in set (0.001 sec)

MariaDB [(none)]> 

6、主服务器数据解锁表

登录主服务器数据库

[root@node0 ~]# /usr/local/mysql/bin/mysql
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 13
Server version: 10.3.7-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)]> unlock tables;
Query OK, 0 rows affected (0.000 sec)

MariaDB [(none)]> 

三、测试主从

1、几个配置参数：

主服务器上的配置参数：

binlog-do-db= ：仅同步指定的库

binlog-ignore-db= ：忽略指定的库

从服务器上的配置参数：

replicate_do_db=

replicate_ignore_db=

replicate_do_table=

replicate_ignore_db=

replicate_wild_do_table= :支持通配符

replicate_wild_ignore_table=

2、测试

主服务器：

MariaDB [(none)]> insert into test001.test(id,name) values(1,"hello world");
Query OK, 1 row affected (0.018 sec)

MariaDB [(none)]> use test001;
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 [test001]> select * from test;
+------+-------------+
| id   | name|
+------+-------------+
|1 | hello world |
+------+-------------+
1 rows in set (0.001 sec)

MariaDB [test001]> 

从服务器：

MariaDB [(none)]> use test001;
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 [test001]> select * from test;
+------+-------------+
| id   | name|
+------+-------------+
|1 | hello world |
+------+-------------+
1 row in set (0.001 sec)

MariaDB [test001]> 

OK成功。主添加数据，从也跟着添加。