MySQL主从同步配置

在我写的这篇《Linux上MySQL搭建》有个疑问。为何我下载的安装包里没有my.cnf配置文件？

然后我尝试搜索了一下这个问题。文章在这。

简单的讲my.cnf文件就是把在命令行上启动MySQL时后面的参数用cnf文件配置好，那么下载启动时就不再需要在命令上加如参数。

当然可以找一个完整的my.cnf之后在上边进行修改自己需要的配置，但我搜了几个发现里边的内容尤其路径不一致。

然后，我转眼还是在《Linux上MySQL搭建》的基础长继续追加我需要的配置，不一定能成功，但不试怎么会知道呢？

-------------------------

先规划一下部署。要搭建一主多从至少需要3台Linux。但我这次只想弄个一主一从，因为从的配置无非是id不同就可以了。

而且主要想看的是主从之间数据同步的问题。最主要的原因是电脑太卡。

《Linux上MySQL搭建》之前搭建好的，IP为192.168.160.130。然后可以在搭建一套，或者直接将130克隆一份。

现在有两个192.168.160.130和192.168.160.131。我让192.168.160.130为主，192.168.160.131为从1。

也就是

name	ip	端口	说明
Master	192.168.160.130		主
slave1	192.168.160.131		从1

--------------

首先需要安装半同步插件。

#半同步插件的安装
在Master上执行：
mysql> INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';
各个Slave上执行：
mysql> INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';

在 192.168.160.130执行。

[root@localhost bin]# ./mysql -u root -p 
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 5.7.20 MySQL Community Server (GPL)

Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

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

mysql> INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';
Query OK, 0 rows affected (1.02 sec)

mysql> 

在192.168.160.131执行

[root@localhost Desktop]# cd /usr/local/software/mysql/bin/
[root@localhost bin]# ./mysql -uroot -proot
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.20 MySQL Community Server (GPL)

Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

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

mysql> INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';
Query OK, 0 rows affected (0.08 sec)

mysql>

---------

1、master

my.cnf

[mysqld]
#binlog 格式 
binlog-format=ROW 
log-bin=mysql-master-bin
#slave更新时是否记录到日志中
#binlog-slave-updates=true
server-id       = 130  
#需要同步的二进制数据库名；
binlog-do-db=rorodb
#开启半同步
rpl_semi_sync_master_enabled=ON
#slave不需要同步数据库
binlog-ignore-db=information_schema
binlog-ignore-db=mysql
binlog-ignore-db=performance_schema

1.2、创建用户用于主从同步的数据库
创建slave1账户 密码123456  ip 192.168.160.131可以访问

[root@localhost bin]# ./mysql -u root -p 
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 5.7.20 MySQL Community Server (GPL)

Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

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

mysql> INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';
Query OK, 0 rows affected (1.02 sec)

mysql> grant replication slave,super,reload on *.* to slave1@192.168.160.131 identified by '123456';
Query OK, 0 rows affected, 1 warning (0.24 sec)

1.3 、查看状态

mysql> show master status;
+-------------------------+----------+--------------+---------------------------------------------+-------------------+
| File                    | Position | Binlog_Do_DB | Binlog_Ignore_DB                            | Executed_Gtid_Set |
+-------------------------+----------+--------------+---------------------------------------------+-------------------+
| mysql-master-bin.000001 |      154 | rorodb       | information_schema,mysql,performance_schema |                   |
+-------------------------+----------+--------------+---------------------------------------------+-------------------+
1 row in set (0.00 sec)

1.4 查看已连接的slave节点数据库

mysql> show slave hosts;
Empty set (0.00 sec)

Empty是因为此时没有从服务器，如若2.3执行完成，则

mysql> show slave hosts;
+-----------+------+------+-----------+--------------------------------------+
| Server_id | Host | Port | Master_id | Slave_UUID                           |
+-----------+------+------+-----------+--------------------------------------+
|       131 |      | 3306 |       130 | 7928c54c-785d-11e8-a1ce-000c293b0ff6 |
+-----------+------+------+-----------+--------------------------------------+
1 row in set (0.00 sec)

1.5 查看binlog日志

mysql> show binary logs;
+-------------------------+-----------+
| Log_name                | File_size |
+-------------------------+-----------+
| mysql-master-bin.000001 |       154 |
+-------------------------+-----------+
1 row in set (0.00 sec)

2.从数据库配置 （slave1）

mysql> show binary logs;
+-------------------------+-----------+
| Log_name                | File_size |
+-------------------------+-----------+
| mysql-master-bin.000001 |       154 |
+-------------------------+-----------+
1 row in set (0.00 sec)

mysql> show binary logs;
+-------------------------+-----------+
| Log_name                | File_size |
+-------------------------+-----------+
| mysql-master-bin.000001 |       154 |
+-------------------------+-----------+
1 row in set (0.00 sec)

2.1 my.cnf 文件配置

[mysqld]
log-bin=mysql-slave-bin
binlog_format=mixed
#开启半同步
rpl_semi_sync_slave_enabled=1
server-id       = 131
replicate-do-db=rorodb
#slave不需要同步数据库
replicate-ignore-db=information_schema
replicate-ignore-db=mysql
replicate-ignore-db=performance_schema

2.2 修改master指向

mysql> change master to master_host='192.168.160.130', master_user='slave1', master_password='123456';
Query OK, 0 rows affected, 2 warnings (0.05 sec)

2.3 启动或者关闭 slave 节点

mysql>start slave;
mysql>start stop;

2.4 查看节点状态

mysql> show slave status \G;

3.测试

在master里操作；

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)

mysql> CREATE DATABASE rorodb;
Query OK, 1 row affected (0.06 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| rorodb             |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

在 slave1里查询；

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| rorodb             |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

也就是说，我在master里操作了东西，备份到了slave1里。

--------------------------------

最后参考资料

https://blog.csdn.net/wwuPower/article/details/78167264

https://blog.csdn.net/m0_37814112/article/details/78606593