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程序
①进入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
#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
#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上导入该数据库文件
#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权限.
mysql>grant replication slave on *.* to ‘testdbakup‘@‘192.168.1.13‘ identified by ‘123456‘;
2, 拷贝生成my.cnf文件
#cp /usr/share/mysql/my-medium.cnf /etc/my.cnf
3，编辑my.cnf文件#vi /etc/my.cnf 注意以下内容
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，
mysql>show master status;记录下File和position,从服务器的设置里会用到该参数. +--------------------------+-------------+---------------------+----------------------------+ | File | Position | Binlog_Do_DB| Binlog_Ignore_DB | +--------------------------+-------------+---------------------+----------------------------+ | mysql-bin.000004 | 107 | testc | | +---------------------------+------------+---------------------+----------------------------+
三 备用mysql服务器（如B13）设置
1，拷贝生成my.cnf文件
#cp /usr/share/mysql/my-medium.cnf /etc/my.cnf
2，编辑my.cnf文件vi /etc/my.cnf含有以下内容
server-id=2 （默认为注释，需修改） 把默认的server-id=1注释掉
3，change master
重启动mysql数据库
# 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，检验配置成功
①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 看到以上数据则表示为进程正常启动
②
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
其中保持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创建备份用户并授权
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上设置:
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依赖的同步服务可以会出现无法正常运行的情况。手动配置两个服务运行后，可恢复同步。未同步的历史数据也会被处理掉。
详见http://www.jb51.net/article/27220.htm
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
表的字符集不一致，主外键会失败
原文：http://davidsolomon.blog.51cto.com/3295360/1611815