Mysql搭建备库

Mysql搭建备库

1.mysql安装

这个步骤不再赘述，如有不明白，请查看我的博客：https://blog.csdn.net/liu16659/article/details/81053412

2.环境

• ip:192.168.211.4，主机名：server4，mysql-5.7.22 做master
• ip:192.168.211.5，主机名：server5，mysql-5.7.22 做slave

3.Master配置

• 查看master 节点上的my.cnf的配置

# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html

[mysqld]
# common
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
#
# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin
#
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock

# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
# don't validate the password
validate-password=OFF

#replicate
server_id=1

#replicate database
binlog-do-db=test
log-bin=mysql-bin

对上述的两个地方进行说明：

• mysql主备环境中需要一个id来表示谁是主，谁是备。上面的server_id=1表示的就是master的id为1.
• 接着我们需要指定对哪一个库进行备份：binlog-do-db=test
• 最后设置使用同步的方式是log-bin，如下：log-bin=mysql-bin
  登录server4的mysql，然后添加一个用户【用于在从库主机中登录进行同步】:create user 'replicate'@'%' identified by 'replicate';；再对该用户授予复制权限：grant replication slave on *.* to 'replicate'@'%';【我以为这里可以更进一步写成：grant replication slave on test.* to 'replicate'@'%';】
  最后查看一下主库的状态：

mysql> show master status\G;
*************************** 1. row ***************************
             File: mysql-bin.000001
         Position: 355
     Binlog_Do_DB: test
 Binlog_Ignore_DB: 
Executed_Gtid_Set: 
1 row in set (0.00 sec)

ERROR: 
No query specified

• file：表示同步的bin-log信息从哪个文件开始；
• position：表示从file的哪个位置开始；
• binlog_do_db：表示同步哪一个库
  在后面的slave配置中，这三个结果都需要使用到。

4.Slave配置

接着查看slave的配置，如下：

[root@server5 mysql]# cat /etc/my.cnf
····
#replicate
server_id=2

#replicate database
replicate-do-db=test

只添加了两行语句：server_id代表slave的id；replicate-do-db表示的是具体的复制库。
接着登录slave 节点的mysql，并执行如下语句：

mysql> change master to master_host='server4',master_port=3306,master_user='replicate',master_password='replicate',master_log_file='mysql-bin.000001',master_log_pos=355;
Query OK, 0 rows affected, 2 warnings (0.01 sec)

上面这个语句我就不再解释了，应该都能懂。接着开启从库：

mysql> start slave;
Query OK, 0 rows affected (0.01 sec)

5.验证

首先在主库上创建test库，

mysql> create database test character set 'utf8';
Query OK, 1 row affected (0.00 sec)

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

mysql> use test;
Database changed
mysql> show tables;
Empty set (0.00 sec)

主库此时仍然没有表，查看从库

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

mysql> use test;
Database changed
mysql> show tables;
Empty set (0.01 sec)

slave节点的test库同样为空。接着在master节点上创建表people，

mysql> create table people(id int ,name varchar(20)) character set 'utf8';
Query OK, 0 rows affected (0.01 sec)

mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| people         |
+----------------+
1 row in set (0.00 sec)

接着在slave上验证：

mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| people         |
+----------------+
1 row in set (0.00 sec)

发现数据已经进行了同步。

至此，mysql的主备已经搭建完毕。

6.注

1. 问题1：上述介绍的是备份某个库的操作，如果需要备份多个库则直接在master的my.cnf中添加新的一行binlog-do-db=test_2等等即可。这行代码的意思是指：同步该库（test_2）的bin-log信息。

2. 问题2：这种直接修改master节点的my.cnf文件去同步某个库，需要重启master才能让整个mysql集群工作，有没有一种方法可以搭建备库，但是不需要重启master？问题2：这种直接修改master节点的my.cnf文件去同步某个库，需要重启master才能让整个mysql集群工作，有没有一种方法可以搭建备库，但是不需要重启master？
   显然是可以的，不过这次备份的库是master的全部库了（因为没有指定具体库的bin-log）， 所以默认就把全部的bin-log都同步了。可以参考如下的步骤：

• step 1：导出Master节点中所有的数据库
  mysqldump -uroot -A --routines --single-transaction --master-data=2 > /data1/mysqldump/alldatabases.sql
  参数注释：

  -A：导出所有库的数据
  --routines Dump stored routines (procedures and functions) from dumped databases
  --single-transaction Issue a BEGIN SQL statement before dumping data from server
  --master-data Write the binary log file name and position to the output
  

这里添加option：--master-data之后，就可以不再使用show master status\G;去查看bin-log的file和position了。

• step 2：
  将上述的sql文件导入到备库中。

• step 3：
  其余步骤同上述内容【在master上创建一个用户，并授权，然后刷新】；在slave上执行change操作。