MySql系统主主复制搭建手册
txt 2019-10-31
Mysql主主复制操作手册
主从复制的原理：（见下图）
第一步：在每个更新数据的事物完成之前，主服务器都会把数据更改记录到二进制日志中。即使事物在执行期间是交错的，mysql也会串行地把事物写入到二进制日志中，写入完成之后，主服务器告诉存储引擎提交事物。
第二步：从服务器把主服务器的二进制日志拷贝到自己的硬盘，即"中继日志"中。首先，它启动一个工作线程，叫I/O从线程。这个I/O线程开启一个普通的客户端连接，然后启动一个特殊的二进制日志存储进程(binlog dump)进程。这个转储进程从主服务器的二进制日志中读取事件，它不会对事物进行轮询。如果它跟上了主服务器，就会进入休眠状态，并等待有新的事件发生时主服务器发出的信号，I/O线程把事件写入到从服务器的中继日志中。
第三步：SQL从线程读取中继日志，并且重放其中的事件，然后更新从服务器的数据。由于这个进程能跟上I/O线程，中继日志通常都在操作系统的缓存中，所有中继日志的开销很低。SQL从线程执行的事件也可以被写入从服务器自己的二进制日志中或是不写。
==重要说明：根据主从复制的原理，做主从复制前，需要保证主从database内容一致==
1.操作系统安装（RH6.3）及配置
完全安装之后需要卸载原来系统中安装的Mysql程序。 查看Mysql的安装
Redhat 6.3卸载如下：
[ ~]# rpm -e mysql-server-5.1.47-4.el6.x86_64 [ ~]# rpm -e --allmatches --nodeps mysql-5.1.47-4.el6.x86_64 [ ~]# rpm -e --allmatches --nodeps mysql-libs-5.1.47-4.el6.x86_64
注：如果是虚拟机装安装时，需要在虚拟机上添加一个硬件网卡。
如果在虚拟机上遇到了mac地址冲突的问题（因为我的虚拟机是拷贝devices eth0 has a different mac than expected ），修改etc/sysconfig/network-scripts/ifcfg-eth0文件中HWDADD=00:0C:29:3B:80改成MACADD=00:0C:29:3A:80，然后重启网卡eth0。
2.主备机Mysql数据库安装与配置
2.1.所需Mysql软件
MySQL-client-community-5.1.46-1.rhel5.i386.rpm
MySQL-server-community-5.1.46-1.rhel5.i386.rpm
2.2.主备机安装Mysql：
将Mysql拷贝到/opt 目录下(目前三屏系统安装的Mysql的版本是5.5.28) 先安装MySQL-server-community-5.1.46-1.rhel5.i386.rpm
执行命令： rpm –ivh MySQL-server-community-5.1.46-1.rhel5.i386.rpm
再安装MySQL-client-community-5.1.46-1.rhel5.i386.rpm
执行命令： rpm –ivh MySQL-client-community-5.1.46-1.rhel5.i386.rpm
截图如下：
2.2.1.启动mysql服务
[ ~]# /etc/init.d/mysql start Starting MySQL... [ OK ]
2.2.2.修改mysql密码
[ ~]# /usr/bin/mysqladmin -u root password root
密码为root
2.23.修改mysql数据库的配置文件
把/usr/share/doc/MySQL-server-5.5.28/my-large.cnf 复制到 /etc/my.cnf
cp /usr/share/doc/MySQL-server-5.5.28/my-large.cnf /etc/my.cnf
修改mysql的编码格式
vi /etc/my.cnf
在[client]下增加
default-character-set=utf8
在[mysqld]下增加
character_set_server=utf8
init_connect=‘SET NAMES utf8‘
设置数据库的表名统一为小写字母 在[mysqld]下增加
lower_case_table_names=1
如图：
重新启动mysql
service mysql restart
如果出现下面情况，多半是由于配置文件错误，详情可查看mysql错误日志，在/var/lib/mysql/的localhost.localdomain.err里
登录mysql查看mysql的编码格式是否更改
mysql -uroot -proot mysql> show variables like ‘%character%‘;
结果如图
说明编码格式已经更改完成
注意：此处配置了mysql表名小写，可能造成mysql以前的数据库删除时报错，如果数据库创建在修改配置之前可能需要将lower_case_table_names=1注释掉，并重启mysql服务后再删除处理，处理完后须解除注释并重启mysql数据库，此处仅提供一个mysql出错时对错误排查的可能性，如果没有遇到可以不必按照这个步骤处理。
2.2.4.登录mysql创建数据库
[ ~]# mysql -u root -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 5 Server version: 5.5.15 MySQL CommunityServer (GPL) Copyright (c) 2000, 2010, Oracle and/or itsaffiliates. All rights reserved. Oracle is a registered trademark of OracleCorporation and/or its affiliates. Other names may be trademarks of theirrespective owners. Type ‘help;‘ or ‘\h‘ for help. Type ‘\c‘ toclear the current input statement. mysql>
--输入密码后成功连接。
2.2.5.重启mysql服务
[ mysql]# /etc/init.d/mysql restart Shutting down MySQL... [ OK ] Starting MySQL.. [ OK ]
注意：如果需要使用mysql客户端工具远程登录数据库请登录进入mysql后输入 grant all on . to ‘root‘@‘%‘ identified by ‘root‘; 语句，然后就可以远程访问mysql数据库了
3.Mysql数据库同步复制配置
这里的主备机不是指数据库的主备，是指使用时，一般使用的数据库为主机，备机为主机数据库宕机后使用的数据库。同步复制是指两个数据库任何一个数据库的数据发生改变，另外一个数据库会随之变化。
两个数据库的配置是相同的：通过指定另一个数据库的地址、用户名（拥有可复制数据权限）、密码来同步数据。
a)主备机mysql初始化操作——添加拥有复制权限的用户
启动mysql，设置初始密码，生成同步复制用户copy。
执行命令：
service mysql restart mysql mysql> use mysql mysql> update user set password=password(‘root‘) where user=‘root‘; mysql> grant replication slave on *.* to ‘copy‘@‘%‘ identified by ‘root‘; mysql> flush privileges;
然后：
service mysql restart
操作截图：
b)主备机同步复制配置
在主机的mysql配置文件中加入同步复制的配置。
执行命令：
vi /etc/my.cnf
server-id = 1 （该内容在my.cnf有，直接将注释打开即可)
log-bin=mysql-bin
auto-increment-increment = 2
auto-increment-offset = 2
binlog-do-db=testdb #记录日志需要同步的数据库名(多个写多行)
replicate-do-db=testdb #需要同步的数据库名（多个写多行）
如主机：
在备机的my.cnf配置文件中加入同步复制的配置。
执行命令：
vi /etc/my.cnf
server-id = 2 #不能与主机相同
log-bin=mysql-bin
auto-increment-increment = 2
auto-increment-offset = 2
binlog-do-db=testdb #修改为实际数据库名（多个写多行）
replicate-ignore-db=mysql
replicate-do-db=testdb #修改为实际数据库名（多个写多行）
如备机：
4.主机到备机的同步调试
a)登录主机：
mysql -uroot -proot mysql> show master status;
File和Position的值用于下一步备机配置。
+------------------+----------+--------------+------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +------------------+----------+--------------+------------------+ | mysql-bin.000010 | 106 | testdb | | +------------------+----------+--------------+------------------+ 1 row in set (0.00 sec)b)
登录备机(192.166.68.127)
截图如下：
b)登录备机
执行如下命令：
mysql -uroot -proot mysql> slave stop; #先停止 slave 服务 mysql> CHANGE MASTER TO MASTER_HOST=‘192.166.68.50‘,MASTER_USER=‘copy‘,MASTER_PASSWORD=‘root‘,MASTER_PORT=3306,MASTER_LOG_FILE=‘mysql-bin.000010‘,MASTER_LOG_POS=106;
MASTER_HOST=‘192.166.68.50‘修改为实际的主服务器IP
注：MASTER_LOG_FILE和MASTER_LOG_POS指的是上一步得到的File和Position的值。
mysql>slave start; #启动slave服务
mysql> show slave status\G; #查看slave状态
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: host1
Master_User: copy
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000010
Read_Master_Log_Pos: 106
Relay_Log_File: webSer1-relay-bin.000086
Relay_Log_Pos: 251
Relay_Master_Log_File: mysql-bin.000010
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB: testdb
Replicate_Ignore_DB: mysql
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 106
Relay_Log_Space: 553
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
1 row in set (0.00 sec)
ERROR:
No query specified 注意：Master_Log_File、Read_Master_Log_Pos和Relay_Master_Log_File的值，slave机器在设置时必须保持与master机器一致。否则不能同步。
同时Slave_IO_Running和Slave_SQL_Running，如果都是yes，则说明同步成功，此时主机到备机的调试成功。
c)验证同步功能是否配置成功，登录主机192.166.68.50数据库testdb，创建一张test表，会自动在备机数据库也创建张该表。
5.备机到主机的同步调试
a)登陆备机：
mysql -uroot -proot mysql> show master status; # ( 红色数字不同主机结果不同 ) +------------------+----------+--------------+------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +------------------+----------+--------------+------------------+ | mysql-bin.000004 | 853 | testdb | | +------------------+----------+--------------+------------------+ 1 row in set (0.00 sec)1 row in set (0.00 sec)
登录备机(192.166.68.127)
截图如下：
b)登录主机
执行如下命令：
mysql -uroot -proot
mysql> slave stop; #先停止 slave 服务
mysql> CHANGE MASTER TO MASTER_HOST=‘192.166.68.127‘,MASTER_USER=‘copy‘,MASTER_PASSWORD=‘root‘,MASTER_PORT=3306,MASTER_LOG_FILE=‘mysql-bin.000004‘,MASTER_LOG_POS=853; # MASTER_HOST=‘192.166.68.127‘修改为实际的备服务器IP
mysql>slave start; #启动slave服务
mysql> show slave status\G; #查看slave状态
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.166.68.127
Master_User: copy
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000004
Read_Master_Log_Pos: 853
Relay_Log_File: webser0-relay-bin.000082
Relay_Log_Pos: 251
Relay_Master_Log_File: mysql-bin.000004
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB: testdb
Replicate_Ignore_DB: mysql
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 853
Relay_Log_Space: 553
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
1 row in set (0.00 sec)ERROR:
No query specified注意：Master_Log_File、Read_Master_Log_Pos和Relay_Master_Log_File的值，slave机器在设置时必须保持与master机器一致。否则不能同步。
同时Slave_IO_Running和Slave_SQL_Running，如果都是yes，则说明同步成功，此时主机到备机的调试成功。
c)验证同步功能是否配置成功，登录备机192.166.68.127数据库testdb，创建一张test1表，会自动在主机数据库也创建张该表。 至此，mysql数据库同步配置完成。
