mysql主从搭建

tpl01	NAT	29.159	mysql(源码）	/etc/my.cnf	提供mysql服务（主）	yw008	数据库主从搭建	mysql 5.6.39
tpl02	NAT	29.152	mysql(源码）	/etc/my.cnf	mysql服务（从）	yw008	数据库主从搭建	mysql 5.6.39

1.参考其他博客在 tpl01, tpl02 编译安装mysql服务

2. 给数据库设置密码：

方法一： 用root进入mysql后

mysql > set password=password('你的密码');

mysql > flush privileges;

步骤一：初始化现有库

    为了在启用binlog日志及同步之前保持主，从库的一致性，建议备份主服务器上现有的库，然后导入到从服务器上。

可以通过mysqldump工具来实现库的导出，导入。

1)备份 MySQL Master(tpl01) 上现有的库

如果服务器已经启动 binlog, 建议对日志做一次重置，否则可忽略：

mysql > reset master;  //重置binlog日志， 以数据库用户root登入

以备份mysql库，test库为例，导出操作如下：

mysqldump -u root -p -B mysql test > /root/mytest.sql

ls -lh /root/mytest.sql

2) 在MySQL Slave(tpl02) 上导入备份的库

 先清理目标库，避免导入时冲突。主要是采用InnoDB引擎的库。

mysql > drop database test; //删除test库等， 以数据库用户root 登入

使用scp 工具下载备份文件

scp tpl01:/root/mytest.sql  ./

mysql -uroot -p < mytest.sql

导入成功后，可重新登入mysql > 环境，确认清理的目标库已恢复。

mysql > show databases;

步骤二： 配置MySQL Master(主服务器，tpl01)

1) 修改/etc/my.cnf 配置，重新启动MySQL服务程序

1. [mysqld]
2. log_bin=dbsvr1-bin                     //启用binlog日志，并指定文件名前缀
3. server_id = 10                         //指定服务器ID号
4. innodb_flush_log_at_trx_commit=1     //优化设置
5. sync-binlog=1                         //允许日志同步
6. .. ..
7. sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
8. explicit_defaults_for_timestamp=1

重启mysql服务

    service mysql restart

2）新建一个备份用户，授予复制权限

需要的权限为 REPLICATION SLAVE,允许Slave服务器访问。

mysql > grant replication slave on *.* to 'replicater'@'192.168.29.%' identified by '123qwe';

3) 检查Master服务器的同步状态

在已经初始化现有库的情况下，查看MASTER状态，记录下当前日志文件名，偏移的位置（下面slave 发起复制时需要用到）：

mysql > show master status\G

步骤三： 配置MySQL (从服务器 tpl02)

1) 修改 /etc/my.cnf 配置，重启MySQL服务器

1. [root@dbsvr2 ~]# vim /etc/my.cnf
2. [mysqld]
3. log_bin=dbsvr2-bin                     //启动SQL日志，并指定文件名前缀
4. server_id = 20                         //指定服务器ID号，不要与Master的相同
5. innodb_flush_log_at_trx_commit=1     //优化设置
6. sync-binlog=1                         //允许日志同步
7. log_slave_updates=1                 //记录从库更新，便于实现“主-从-从”链式复制
8. .. ..
9. sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
10. explicit_defaults_for_timestamp=1

在生产环境中，还可以根据需要设置更详细的同步选项。 如 指定当主，从网络中断时的超时重试时间（slave-net-timeout=60)等，可参考MySQL手册

配置完成后，重启mysql 服务：

service mysql restart

2)登入 mysql >  环境， 发起同步操作

通过change master 语句指定 master 服务器的IP地址， 同步用户名/密码，起始日志文件，偏移位置

    

1. mysql> CHANGE MASTER TO MASTER_HOST='192.168.29.159',
2. -> MASTER_USER='replicater',
3. -> MASTER_PASSWORD='123qwe',
4. -> MASTER_LOG_FILE='tpl01-bin.000001',     //对应Master的日志文件
5. -> MASTER_LOG_POS=335;

然后执行start slave 启动复制：

mysql > start slave;

通过上述操作，MASTER服务器的设置信息自动存为master.info文件，以后每次MySQL服务器程序时会自动调用并更新。

3）检查Slave 服务器的同步状态

通过show slave status 语句可查看从服务器状态，确认其中的IO线程，SQL线程正常运行，才能成功同步：

mysql > show slave status\G

若IO线程或SQL线程有一个为 No,  则应检查服务器的错误日志，分析并排出故障后重启主从复制。

步骤四：测试主从同步效果

1）在Master上操作数据库，表，表记录

  新建newdb库，newtable 表，随意插入几条表记录。

2）在slave上确认自动同步的结果

 直接切换到newdb库，并查询newtable表的记录，应该与Master上的一样，这才说明主从同步已经成功生效。

3）在Master服务器上可查看Slave主机的信息

mysql > show slave hosts;

步骤五：将Slave 服务器设为只读

一般来说，为了避免写入冲突，采用主从复制结构时，不应该允许用户从Slave执行数据库写入操作，这样会导致双方数据的不一致性。

正因为如此，我们可以把slave数据库限制为只读模式，这种情况下有 SUPER权限的用户和 SLAVE 同步线程才能写入。

相关验证操作集效果可参考以下过程。

1） 新建一个测试用户rwuser(不能用root测试）

mysql > grant all on newdb.* to rwuser@localhost identified by '123qwe';

2) 未启用只读前，验证Slave 写入

在slave上以rwuser登入（不要root)

mysql -u rwuser -p

然后向newdb库中新建一个booker表

mysql > use newdb;

mysql > create table booker(id int(12));

在slave 上可看到新建的booker表：

mysql > show tables;

但是在Master上却看不到，导致主从上的newdb出现不一致：

mysql > use newdb;

mysql > show tables;

完成上述验证后，在slave删除booker表，确保双方一致：(不删除下面实验还是能进行下去的）

mysql > drop table booker;

3) 修改/etc/my.cnf 文件，重载配置

vim /etc/my.cnf

[mysqld]

read_only=1

~] service mysql restart

4)再次在Slave 验证数据库写入操作

仍然是以rwuser(不要用root)来验证，当尝试创建表时被拒绝：

mysql > use newdb;

mysql > create table booker(id int(12));  //失败

mysql > drop table mytable;                 //失败