Mysql主从同步

事前准备

关闭selinux

setenforce 0
vim /etc/sysconfig/selinux 
SELINUX=disabled

配置MySQL yum源

vim /etc/yum.repos.d/mysql.repo
[mysql57]
name=mysql57
baseurl=https://mirrors.tuna.tsinghua.edu.cn/mysql/yum/mysql57-community-el7/
gpgcheck=0
enabled=1

安装

yum install mysql-community-server

初始化

mysqld --initialize-insecure --user=mysql  --datadir=/var/lib/mysql

【主库】操作及配置

配置my.cnf

/etc/my.cnf
[client]
port = 3306
default-character-set = utf8mb4

[mysql]
port = 3306
default-character-set = utf8mb4

[mysqld]
##########################
# summary
##########################
#bind-address = 0.0.0.0
#port = 3306
datadir=/datavol/mysql/data  #数据存储目录，根据实际情况而定，在docker中是指定其他目录了，这个目录没用使用，但是若不是docker的话则需要指定这个配置

##########################
# log bin
##########################
server-id = 100             #必须唯一
log_bin = mysql-bin         #开启及设置二进制日志文件名称
binlog_format = MIXED
sync_binlog = 1
expire_logs_days =7         #二进制日志自动删除/过期的天数。默认值为0，表示不自动删除。

#binlog_cache_size = 128m
#max_binlog_cache_size = 512m
#max_binlog_size = 256M

binlog-do-db = test         #要同步的数据库 

binlog-ignore-db = mysql    #不需要同步的数据库 
binlog_ignore_db = information_schema
binlog_ignore_db = performation_schema
binlog_ignore_db = sys
            

##########################
# character set
##########################
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci

安装启动主库

systemctl start mysqld

创建授权用户

连接mysql主数据库，键入命令mysql -u root -p，输入密码后登录数据库。创建用户用于从库同步复制，授予复制、同步访问的权限

mysql> CREATE USER 'slave'@'%' IDENTIFIED BY '123456';
Query OK, 0 rows affected (0.01 sec)

mysql> GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'slave'@'%';
Query OK, 0 rows affected (0.00 sec)

log_bin是否开启

mysql> show variables like 'log_bin';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin       | ON    |
+---------------+-------+
1 row in set

查看master状态

mysql> show master status;
+------------------+----------+--------------+--------------------------------------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB                                 | Executed_Gtid_Set |
+------------------+----------+--------------+--------------------------------------------------+-------------------+
| mysql-bin.000001 |     154  | test         | mysql,information_schema,performation_schema,sys |                   |
+------------------+----------+--------------+--------------------------------------------------+-------------------+
1 row in set

!!!注意：mysql-bin.000001 跟154这俩参数从库会使用到，根据实际情况修改

【从库】配置及操作

配置my.cnf

/etc/my.cnf
[client]
port = 3306
default-character-set = utf8mb4

[mysql]
port = 3306
default-character-set = utf8mb4

[mysqld]
##########################
# summary
##########################
#bind-address = 0.0.0.0
#port = 3306
datadir=/datavol/mysql/data     #数据存储目录，根据实际情况而定，在docker中是指定其他目录了，这个目录没用使用，但是若不是docker的话则需要指定这个配置

##########################
# log bin
##########################
server-id = 110
        

##########################
# character set
##########################
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci

安装启动从库

 同上

设置主库信息

登录【从数据库】，进入mysql命令行。

mysql> stop slave;
Query OK, 0 rows affected

mysql> CHANGE MASTER TO MASTER_HOST='172.16.1.51',
MASTER_PORT=3306,
MASTER_USER='slave',
MASTER_PASSWORD='123.com',
MASTER_LOG_FILE='mysql-bin.000001', # 主库信息根据主库信息填写
MASTER_LOG_POS=154;					# 主库信息根据主库信息填写
Query OK, 0 rows affected

mysql> start slave;
Query OK, 0 rows affected

stop slave; //停止同步
start slave; //开始同步

//必须和【主数据库】的信息匹配。
CHANGE MASTER TO
MASTER_HOST=‘172.16.1.51’, //主库IP
MASTER_PORT=3306, //主库端口
MASTER_USER=‘slave’, //访问主库且有同步复制权限的用户
MASTER_PASSWORD=‘123456’, //登录密码
//【关键处】从主库的该log_bin文件开始读取同步信息，主库show master status返回结果
MASTER_LOG_FILE=‘mysql-bin.000001’,
//【关键处】从文件中指定位置开始读取，主库show master status返回结果
MASTER_LOG_POS=154;

mysql> show slave status \G

 Slave_IO_Running: Yes
 Slave_SQL_Running: Yes

只有【Slave_IO_Running】和【Slave_SQL_Running】都是Yes，则同步是正常的

mysql> show variables like 'log_error%';
+---------------------+--------+
| Variable_name       | Value  |
+---------------------+--------+
| log_error           | stderr |
| log_error_verbosity | 3      |
+---------------------+--------+
2 rows in set
配置完成，则主从数据库开始自动同步。

验证数据同步

建库

使用root账号登录【主服务器】，创建test数据库

mysql> CREATE DATABASE test;
Query OK, 1 row affected (0.00 sec)

mysql> USE test;
Database changed

建表

在【主库】中创建user表

CREATE TABLE `user` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`name` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL,
`age` tinyint(3) unsigned NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

新增

在【主库】中向user表插入一条数据：

mysql> use test;
Database changed
mysql> INSERT INTO user (id, name, age) VALUES (1, '卡尔', 18);
Database changed
mysql> SELECT * FROM user;
+----+------+-----+
| id | name | age |
+----+------+-----+
|  1 | 卡尔 |  18 |
+----+------+-----+
1 row in set

在【从库】中查询user表数据：

mysql> use test;
Database changed
mysql> SELECT * FROM user;
+----+------+-----+
| id | name | age |
+----+------+-----+
|  1 | 卡尔 |  18 |
+----+------+-----+
1 row in set
新增记录同步成功。

更新

在【主库】中修改刚才插入的数据：

问题：在docker环境中登陆mysql，输入中文的话不显示，就比如下面这个，复制后粘贴到MySQL命令行中执行，卡尔这俩字不显示，是空的，有待进一步研究

mysql> UPDATE user SET name='卡尔' where id=1;
Database changed
Rows matched: 1  Changed: 1  Warnings: 0
mysql> SELECT * FROM user;
+----+------+-----+
| id | name | age |
+----+------+-----+
|  1 | 卡尔 |  18 |
+----+------+-----+
1 row in set

在【从库】中查询user表数据：

mysql> SELECT * FROM user;
+----+------+-----+
| id | name | age |
+----+------+-----+
|  1 | 卡尔 |  18 |
+----+------+-----+
1 row in set
更新记录同步成功。

删除

在【主库】中删除刚才更新的数据：

mysql> DELETE FROM user WHERE id=1;
Database changed
mysql> SELECT * FROM user;
Empty set
在【从库】中查询user表数据：

mysql> SELECT * FROM user;
Empty set
删除记录同步成功。