mysql 主从

21321

                      Mysql主备配置
说明
Mysql服务器为linux系统（如centOS5.8），运行PAVLL软件发布包中install.sh 文件后可选择安装mysql，安装版本为5.5.27。
mysql服务器1:192.168.12.33  下文简称A33
mysql服务器2:192.168.1.13   下文简称B13
下文中先配置A33做为主用机数据同步到作为备用机B13，后配置B13上数据同步到A33，使得A33和B13互为主备。
一 准备
1,修改A33和B13的iptables服务（防火墙）开放Mysql端口3306允许远程访问
  ①修改防火墙配置文件 #vi /etc/sysconfig/iptables
增加下面一行：-A RH-Firewall-1-INPUT -m state --state NEW -m tcp -p tcp --dport 3306 -j ACCEPT
②配置后，重新启动iptable服务# service iptables restart
2,A33和B13均开放mysql远程互访问权限，可互相访问对方mysql程序

1 2 3 4 5 6 7 8

①进入mysql #mysql -h localhost -u root     ②赋予任何主机使用root/123456访问mysql的权限 mysql>GRANT ALL PRIVILEGES ON *.* TO 'root'@'%'IDENTIFIED BY '123456' WITH GRANT OPTION 出于安全考虑一般对特定IP开放权限： GRANT ALL PRIVILEGES ON *.* TO 'root'@'192.168.1.13'IDENTIFIED BY '123456' WITH GRANT OPTION;      ③使修改生效 mysql>FLUSH PRIVILEGES;

   3，A33和B13的手动同步database，其中testd：testa,testb,notify ；testc：testc
      ①在A33上，导出database

1 2 3 4

#mysqldump --user="root" --password=123456 --host=localhost testa > testa.sql; #mysqldump --user="root" --password=123456 --host=localhost testb > testb.sql; #mysqldump --user="root" --password=123456 --host=localhost notify > notify.sql; #mysqldump --user="root" --password=123456 --host=localhost testc > testc.sql;

      ②在B13上，删除其原来testd和testc使用的database

1 2 3 4

#mysql --user=root --password=123456 --host=localhost --execute="drop database if exists testa;CREATE DATABASE testa"; #mysql --user=root --password=123456 --host=localhost --execute="drop database if exists testb;CREATE DATABASE testb"; #mysql --user=root --password=123456 --host=localhost --execute="drop database if exists notify;CREATE DATABASE notify"; #mysql --user=root --password=123456 --host=localhost --execute="drop database if exists testc;CREATE DATABASE testc";

      ③将①中导出的文件拷贝到B13上，然后在B13上导入该数据库文件

1 2 3 4

#mysql --user=root --password=123456 --host=localhost testa < testa.sql; #mysql --user=root --password=123456 --host=localhost testb < testb.sql; #mysql --user=root --password=123456 --host=localhost notify < notify.sql; #mysql --user=root --password=123456 --host=localhost testc < testc.sql;

          注：运行命令的文件目录为存放数据文件的目录
二 主mysql服务器（如A33）配置
1,进入mysql后创建一个复制用户,具有replication slave权限.

1	mysql>grant replication slave on *.* to 'testdbakup'@'192.168.1.13' identified by '123456';

2, 拷贝生成my.cnf文件

1	#cp /usr/share/mysql/my-medium.cnf    /etc/my.cnf

3，编辑my.cnf文件#vi  /etc/my.cnf  注意以下内容

1 2 3 4 5 6

server-id=1  （默认就有，保持注释server-id=2） log-bin=mysql-bin（默认就有） binlog-do-db=testc （按需增加） binlog-do-db=testa  （按需增加） binlog-do-db=testb    （按需增加） binlog-do-db=notify （按需增加）

保存修改后重启mysql服务 # service mysql restart
4，

1 2 3 4 5 6

mysql>show master status;记录下File和position,从服务器的设置里会用到该参数. +--------------------------+-------------+---------------------+----------------------------+ | File                       | Position  | Binlog_Do_DB| Binlog_Ignore_DB | +--------------------------+-------------+---------------------+----------------------------+ | mysql-bin.000004 |      107   | testc                |                               | +---------------------------+------------+---------------------+----------------------------+

三 备用mysql服务器（如B13）设置
1，拷贝生成my.cnf文件

1	#cp /usr/share/mysql/my-medium.cnf    /etc/my.cnf

2，编辑my.cnf文件vi /etc/my.cnf含有以下内容

1 2	server-id=2 （默认为注释，需修改） 把默认的server-id=1注释掉

3，change master
重启动mysql数据库

1 2 3 4

# service mysql restart mysql>stop slave; mysql>change master to master_host='192.168.12.33',master_user='testdbakup',master_password='123456',ma        ster_log_file='mysql-bin.000004',master_log_pos=107 ; mysql>start slave;

4，检验配置成功

1 2 3 4 5 6 7 8 9 10 11

①mysql>show processlist\G; *************************** 3. row ***************************      Id: 10    User: system user    Host:      db: NULL Command: Connect    Time: 380   State: Slave has read all relay log; waiting for the slave I/O thread to update it    Info: NULL 看到以上数据则表示为进程正常启动

②

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45

mysql>show slave status\G;(以下为正常数据) *************************** 1. row ***************************                Slave_IO_State: Waiting for master to send event                   Master_Host: 192.168.12.33                   Master_User: testdbakup                   Master_Port: 3306                 Connect_Retry: 60               Master_Log_File: mysql-bin.000007           Read_Master_Log_Pos: 228                Relay_Log_File: localhost-relay-bin.000002                 Relay_Log_Pos: 374         Relay_Master_Log_File: mysql-bin.000007              Slave_IO_Running: Yes             Slave_SQL_Running: Yes               Replicate_Do_DB:           Replicate_Ignore_DB:            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: 228               Relay_Log_Space: 534               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:   Replicate_Ignore_Server_Ids:              Master_Server_Id: 1 1 row in set (0.00 sec) ERROR: No query specified

四 配置备用mysql（如B13）的数据同步到主机mysql（A33）
   如果做该设置,则2个mysql服务器则互为主备配置，双向同步数据
在 B13上设置
1，编辑/etc/my.cnf

1 2 3 4 5 6

其中保持server-id=2 按需添加binlog-do-db=testc binlog-do-db=testa binlog-do-db=testb binlog-do-db=notify 保存修改完后重启mysql服务：# service mysql restart

2，为A33创建备份用户并授权

1 2 3 4 5 6 7

mysql>grant replication slave on *.* to 'testdbakup'@'192.168.12.33' identified by '123456'; 3，mysql>show master status;记录下File和position,A33的设置里会用到该参数. +--------------------------+-------------+---------------------+----------------------------+ | File                       | Position  | Binlog_Do_DB| Binlog_Ignore_DB | +--------------------------+-------------+---------------------+----------------------------+ | mysql-bin.000005 |      107   | testc                |                               | +---------------------------+------------+---------------------+----------------------------+

在A33上设置:

1 2 3 4 5

mysql>stop slave; mysql>change master to master_host='192.168.1.13',master_user='testdbakup',master_password='123456',master_log_file='mysql-bin.000005',master_log_pos=107 ; mysql>start slave;(同上) mysql>show processlist\G;(同上) mysql>show slave status\G;(同上)

互为主备配置检测:
在任一端mysql的testd或testc的database中的表中新增，删除，修改数据,然后查看另一端mysql对应表的数据是否一致。如是则说明数据同步成功主备设置成功。
五F&Q
1 偶现主备数据库所在机器重启后数据库不同步（）
mysql的同步依赖其Slave_IO_Running和Slave_SQL_Running服务，当这两个服务有任意一个都会导致数据无法同步。据大多数mysql同步案例来看，当mysql所在服务器由于宕机、断电等因素重启后，mysql依赖的同步服务可以会出现无法正常运行的情况。手动配置两个服务运行后，可恢复同步。未同步的历史数据也会被处理掉。

JIRA上已有问题单(NANGA-377)跟踪：http://192.168.1.119:8080/browse/NANGA-377






Error Code: 1005 -- there is a wrong primary key reference in your code

表的字符集不一致，主外键会失败