嵌入式开发交流网论坛

标题: linux中部署mysql主从同步示例详解，建议收藏！ [打印本页]

作者: 午夜De凶手 时间: 2020-1-27 04:14
标题: linux中部署mysql主从同步示例详解，建议收藏！
准备两台服务器并安装mysql5.7
master服务器
192.168.254.132
slave服务器
192.168.254.136
确保两台服务器安装的mysql版本一致，本测试使用的是mysql5.7，登录mysql数据库，使用如下命令查看mysql数据库版本
[root@localhost ~]# mysql -u root -p
Enter password:
mysql> show variables like '%version%';

[attach]37097[/attach]

master服务器配置流程
1、登录mysql数据库
[root@localhost ~]# mysql -u root -p
Enter password:

[attach]37098[/attach]

2、创建数据库，数据库名称为：TONGBU
mysql> create database TONGBU;

3、进入TONGBU数据库并创建表TB1
mysql> use TONGBU;
mysql> create table TB1(id varchar(60),name varchar(100));

4、修改配置/etc/my.cnf文件
[attach]37099[/attach]

修改的具体参数如下：
server-id=1
log-bin=mysql-bin
binlog-do-db=TONGBU
binlog-ignore-db=mysql
sync_binlog=1
binlog_checksum=none
binlog_format=mixed

提示
[mysqld] server-id=1 #数据库唯一ID，主从的标识号绝对不能重复。
log-bin=mysql-bin #开启bin-log，并指定文件目录和文件名前缀
binlog-do-db=liting #需要同步liting数据库。如果是多个同步库，就以此格式另写几行即可。如果不指明对某个具体库同步，就去掉此行，表示同步所有库（除了ignore忽略的库）。
binlog-ignore-db=mysql #不同步mysql系统数据库。如果是多个不同步库，就以此格式另写几行；也可以在一行，中间逗号隔开。
sync_binlog = 1 ＃确保binlog日志写入后与硬盘同步
binlog_checksum = none ＃跳过现有的采用checksum的事件，mysql5.6.5以后的版本中binlog_checksum=crc32,而低版本都是binlog_checksum=none
binlog_format = mixed ＃bin-log日志文件格式，设置为MIXED可以防止主键重复。
在主服务器上最重要的二进制日志设置是sync_binlog，这使得mysql在每次提交事务的时候把二进制日志的内容同步到磁盘上，即使服务器崩溃也会把事件写入日志中。
sync_binlog这个参数是对于MySQL系统来说是至关重要的，他不仅影响到Binlog对MySQL所带来的性能损耗，而且还影响到MySQL中数据的完整性。对于"sync_binlog"参数的各种设置的说明如下：
sync_binlog=0，当事务提交之后，MySQL不做fsync之类的磁盘同步指令刷新binlog_cache中的信息到磁盘，而让Filesystem自行决定什么时候来做同步，或者cache满了之后才同步到磁盘。
sync_binlog=n，当每进行n次事务提交之后，MySQL将进行一次fsync之类的磁盘同步指令来将binlog_cache中的数据强制写入磁盘。
在MySQL中系统默认的设置是sync_binlog=0，也就是不做任何强制性的磁盘刷新指令，这时候的性能是最好的，但是风险也是最大的。因为一旦系统Crash，在binlog_cache中的所有binlog信息都会被丢失。而当设置为“1”的时候，是最安全但是性能损耗最大的设置。因为当设置为1的时候，即使系统Crash，也最多丢失binlog_cache中未完成的一个事务，对实际数据没有任何实质性影响。
从相关测试来看，对于高并发事务的系统来说，“sync_binlog”设置为0和设置为1的系统写入性能差距可能高达5倍甚至更多。
5、重置mysql数据库
[root@hadoop4 ~]# systemctl restart mysqld.service

6、授权给slave查同步权限
mysql> grant replication slave on *.* to slave@192.168.254.136 identified by "123456";

注意，注意，注意：：：
授权的用户：slave，不是从服务器的用户名，也不是从服务器中Mysql登录用的用户名，也是主服务器为从服务器自定义开户的一个可供从服务读（或者说是同步数据）数据用的一个用户名，可以自定义，但是要求从服务器在my.cnf配置文件中配置的master-user参数的名称一致，
如：master-user=slave #该名称为主服务器上可以进行同步的用户名，也是在此处授权的用户；
另外这一步容易出现一个异常，异常如下
ERROR 1819 (HY000): Your password does not satisfy the current policy requirements

原因是不满足密码设置的策略，这里需要两个命令来设置一下slave服务器mysql密码设置的策略
mysql> set global validate_password_policy=0;
mysql> set global validate_password_length=1;

[attach]37100[/attach]

执行刷新数据库：
flush privileges;

7、授权成功后，查看主从状态
mysql> show master status;

[attach]37101[/attach]

8、查看二进制日志文件
[root@hadoop4 ~]# find / -name mysql-bin.000001

[attach]37102[/attach]

可以看到二进制日志文件在路径：/var/lib/mysql/mysql-bin.000001下
[root@hadoop4 ~]# ll /var/lib/mysql

[attach]37103[/attach]

9、备份要同步的数据库
1）、数据库备份语法
表结构+数据
mysqldump -u用户名 -p 数据库名 > 数据库名.sql

只导出表结构
mysqldump -u用户名 -p -d 数据库名 > 数据库名.sql如
示例：
#/usr/local/mysql/bin/mysqldump -uroot -p -d abc > abc.sql

执行备份命令前，一定要先到mysql的运行路径下，再执行一下命令，如果不知道可以通过find命令查询:
[root@hadoop4 ~]# find / -name mysqldump

本系统中mysql数据库导出目录如下：
/usr/bin/mysqldump
[attach]37104[/attach]

2）、执行数据库导出命令
[root@hadoop4 ~]# /usr/bin/mysqldump -uroot -p TONGBU > /usr/mytest/TONGBU.sql

[attach]37105[/attach]

10、把导出的数据库文件拷贝到slave（192.168.254.136）服务器上
[root@hadoop4 mytest]# src TONGBU.sql root@192.168.254.136:/usr/mytest/

[attach]37106[/attach]

slave（192.168.254.136）服务器上拷贝成功：
[attach]37107[/attach]

11、slave（192.168.254.136）数据库导入备份脚本
1）、数据库导入语法
使用 mysql 命令导入数据库格式如下：
mysql -u用户名 -p 数据库名 < 数据库名.sql
示例：
# mysql -uroot -p123456 < abc.sql

使用 source 命令导入
先登录到 mysql 中，并创建一个空的数据库：
mysql> create database abc; # 创建数据库
mysql> use abc; # 使用已创建的数据库
mysql> set names utf8; # 设置编码
mysql> source /home/abc/abc.sql # 导入备份数据库

2）、slave（192.168.254.136）数据库具体导入步骤
登录数据库，并创建数据库
[root@localhost mytest]# mysql -u root -p
mysql> create database TONGBU;
Query OK, 1 row affected (0.03 sec)
mysql> use TONGBU;
Database changed
mysql> set names utf8;
Query OK, 0 rows affected (0.00 sec)

[attach]37108[/attach]

执行导入命令如下：
[root@localhost mytest]# mysql -uroot -p TONGBU < /usr/mytest/TONGBU.sql

[attach]37109[/attach]

slave服务器配置流程
1、设置slave数据库的my.cnf配置文件
[root@localhost mytest]# vi /etc/my.cnf

[attach]37110[/attach]

具体配置：
[mysqld]
server-id=2
log-bin=mysql-bin-slave #从主机中可要可不要
replicate-do-db=TONGBU
replicate-ignore-db=mysql
slave-skip-errors = all
master-host=192.168.254.132 #指定主服务器IP地址
master-user=slave #指定定在主服务器上可以进行同步的用户名
master-password=123456 #密码
#master-port=3306
master-connect-retry=60 #断点重新连接时间

提示：
[mysqld]
server-id=2 #设置从服务器id，必须于主服务器不同
log-bin=mysql-bin #启动MySQ二进制日志系统
replicate-do-db=liting #需要同步的数据库名。如果不指明同步哪些库，就去掉这行，表示所有库的同步（除了ignore忽略的库）。
replicate-ignore-db=mysql #不同步test数据库
slave-skip-errors = all #跳过所有的错误，继续执行复制操作
当只针对某些库的某张表进行同步时，如下，只同步liting库的haha表和test库的heihei表：
replicate-do-db = liting
replicate-wild-do-table = liting.haha //当只同步几个或少数表时，可以这样设置。注意这要跟上面的库指定配合使用；
replicate-do-db = test
replicate-wild-do-table = test.heihei //如果同步的库的表比较多时，就不能这样一一指定了，就把这个选项配置去掉，直接根据指定的库进行同步。
重点
master-user=slave #指定定在主服务器上可以进行同步的用户名
master-password=123456 #密码
这两个配置要与主服务器上授权的用户名和密码一致，不到主从无法同步数据，一定要清楚这一点，也是主服务器和从服务器是否能同步的核心要点
mysql> grant replication slave on *.* to slave@192.168.254.136 identified by "123456";

2、设置启用授权用户
mysql>stop slave; #停止slave
mysql> change master to master_host='192.168.254.132',master_user='slave',master_password='123456';
mysql> start slave; #启动slave

3、查看配置状态是否成功
1）、从服务器配置状态查看如下
mysql> show slave status\G

[attach]37111[/attach]

2）、主服务器配置状态查看命令如下：
mysql> show processlist \G

[attach]37112[/attach]

目前主服务器和从服务器的配置已经全部结束，下面我们进行测试
1、进入主服务器，登录mysql数据库
[root@localhost ~]# mysql -u root -p
Enter password:

2、进入TONGBU数据库，并在TB1表中添加一条数据
mysql> use TONGBU;

mysql> insert into TB1 values('10001','testname1');

[attach]37113[/attach]

3、查看主服务器中，TONGBU数据，TB1表中是否添加成功
mysql> select * from TB1;

[attach]37114[/attach]

4、进入从服务器，登录mysql数据库
[root@localhost ~]# mysql -u root -p
Enter password:

5、进入TONGBU数据库，并查询TB1表
mysql> select * from TB1;

[attach]37115[/attach]

可以看到，从服务器已经同步了主服务器的数据库信息，linux下部署mysql主从同步-成功！！！！

欢迎光临嵌入式开发交流网论坛 (http://www.dianzixuexi.com/bbs/)