mysql主从.主主配置详细.docx

查询mysql内所有用户情况mysqlSELECT DISTINCT CONCAT(User: ,user,host,;) AS query FROM mysql.user;如果没有在/etc/f中配置bin-log位置，MySQL的bin-log默认文件位置在/var/lib/mysql下配置时还请注意：1. 主服务器、从服务器的IP地址可能变化，不要在f配置固定的master-ip，用命令指定。2. 主服务器和从服务器都启动ssh服务，方便从服务器远程登录主服务器。3. 在从服务器上测试主服务器的状态。4. mysql配置改变主要要重启服务sudo restart mysql或service mysql restart，我就因为没有及时重启浪费了时间。5. 配置发生改变后最好重新开启一个命令行终端输入命令。6. 从数据库的改变也能同步到主数据库过程1.1.Master 设置步骤1. 配置 f 文件确保主服务器主机上f文件的mysqld部分包括一个log-bin选项。该部分还应有一个server-id=Master_id选项# vim /etc/mysql/fserver-id = 1log_bin = /var/log/mysql/mysql-bin.logexpire_logs_days = 10max_binlog_size = 100Mbinlog_do_db = testbinlog_ignore_db = mysqlbind-address默认是你必须更改它，否则Slave将无法链接到 Master#bind-address= bind-address= 重启服务器neonetkiller:$ sudo /etc/init.d/mysql reload * Reloading MySQL database server mysqld OK 建议使用reload,如果不起作用再用restart2. 登录slave服务器，测试主库3306工作情况，如果看到下面相关信息表示工作正常。3. 4. # telnet 46 33065. Trying 46.6. Connected to 46.7. Escape character is .8. I9. 5.1.61-0ubuntu-log1Wgs/*#p GRANT REPLICATION SLAVE ON *.* TO replication%. IDENTIFIED BY slavepass;14. mysql FLUSH PRIVILEGES;15. GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO replication31 IDENTIFIED BY slavepass16. 锁表禁止写入新数据17. 18. mysql FLUSH TABLES WITH READ LOCK;19. 20. 查看Master 工作状态21. 22. mysql SHOW MASTER STATUS;23. +-+-+-+-+24. | File | Position | Binlog_Do_DB | Binlog_Ignore_DB |25. +-+-+-+-+26. | mysql-bin.000002 | 106 | test | mysql |27. +-+-+-+-+28. 1 row in set (0.00 sec)29. 如果显示下面内容表示，配置不正确mysql SHOW MASTER STATUS;Empty set (0.02 sec)取得快照并记录日志名和偏移量后，可以在主服务器上重新启用写活动mysql UNLOCK TABLES;5.1.2.Slave过程1.2.Slave 设置步骤1. 配置f从服务器的ID必须与主服务器的ID不相同,如果设置多个从服务器，每个从服务器必须有一个唯一的server-id值，必须与主服务器的以及其它从服务器的不相同。# vim /etc/mysql/fmysqldserver-id = 22. # service mysql restart3. mysql start/running, process 228934. 5. 指定 master 相关参数在从服务器上执行下面的语句，用你的系统的实际值替换选项值mysql CHANGE MASTER TO- MASTER_HOST=master_host_name,- MASTER_USER=replication_user_name, - MASTER_PASSWORD=replication_password, - MASTER_LOG_FILE=recorded_log_file_name, - MASTER_LOG_POS=recorded_log_position; CHANGE MASTER TO MASTER_HOST=29, MASTER_USER=replication, MASTER_PASSWORD=slavepass;mysql CHANGE MASTER TO MASTER_HOST=29, MASTER_USER=repl, MASTER_PASSWORD=slavepass;Query OK, 0 rows affected (0.14 sec)6. 启动从服务器线程7. 8. mysql START SLAVE;9. Query OK, 0 rows affected (0.00 sec)10. 11. SLAVE STATUS12. 13. mysql SHOW SLAVE STATUSG14. * 1. row *15. Slave_IO_State: Connecting to master16. Master_Host: 2917. Master_User: repl18. Master_Port: 330619. Connect_Retry: 6020. Master_Log_File:21. Read_Master_Log_Pos: 422. Relay_Log_File: mysqld-relay-bin.00000223. Relay_Log_Pos: 9824. Relay_Master_Log_File:25. Slave_IO_Running: Yes26. Slave_SQL_Running: Yes27. Replicate_Do_DB:28. Replicate_Ignore_DB:29. Replicate_Do_Table:30. Replicate_Ignore_Table:31. Replicate_Wild_Do_Table:32. Replicate_Wild_Ignore_Table:33. Last_Errno: 034. Last_Error:35. Skip_Counter: 036. Exec_Master_Log_Pos: 037. Relay_Log_Space: 9838. Until_Condition: None39. Until_Log_File:40. Until_Log_Pos: 041. Master_SSL_Allowed: No42. Master_SSL_CA_File:43. Master_SSL_CA_Path:44. Master_SSL_Cert:45. Master_SSL_Cipher:46. Master_SSL_Key:47. Seconds_Behind_Master: NULL48. 1 row in set (0.00 sec)49. 5.1.3.Testing1. 登录 master复制进程的信息SHOW PROCESSLIST语句可以提供在主服务器上和从服务器上发生的关于复制的信息mysql SHOW PROCESSLISTG* 1. row * Id: 62 User: replication Host: ken-hx409.local:36465 db: NULLCommand: Binlog Dump Time: 679 State: Has sent all binlog to slave; waiting for binlog to be updated Info: NULL* 2. row * Id: 64 User: root Host: localhost db: NULLCommand: Query Time: 0 State: NULL Info: SHOW PROCESSLIST2 rows in set (0.00 sec)2. 登录从库，查看复制线程3. 4. mysql SHOW PROCESSLISTG5. * 1. row *6. Id: 2737. User: root8. Host: localhost9. db: NULL10. Command: Query11. Time: 012. State: NULL13. Info: SHOW PROCESSLIST14. * 2. row *15. Id: 27616. User: system user17. Host:18. db: NULL19. Command: Connect20. Time: 221. State: Waiting for master to send event22. Info: NULL23. * 3. row *24. Id: 27725. User: system user26. Host:27. db: NULL28. Command: Connect29. Time: 230. State: Has read all relay log; waiting for the slave I/O thread to update it31. Info: NULL32. 3 rows in set (0.00 sec)33. 如果没有复制进程，请使用start slave;启动mysql SHOW PROCESSLISTG* 1. row * Id: 273 User: root Host: localhost db: NULLCommand: Query Time: 0 State: NULL Info: SHOW PROCESSLIST1 row in set (0.02 sec)mysql start slave;Query OK, 0 rows affected (0.00 sec)34. 登录 master35. 36. mysql insert into foo(id,data) values(2,Hello world!);37. Query OK, 1 row affected (0.00 sec)38. 39. 登录 slave40. 41. mysql select * from foo;42. 在master服务器上插入一条记录，你可以立刻在slave服务器上看到变化。5.1.4.将现有数据库迁移到主从结构数据库数据库已经存在的情况下怎么迁移1. Master 锁表禁止写入新数据2. 3. mysql FLUSH TABLES WITH READ LOCK;4. 5. Slave 停止复制进程6. 7. mysql stop slave;8. 9. 备份Master数据库10. 11. mysqldump yourdb | gzip yourdb.sql.gz12. 13. 恢复数据库如果使用mysqldump备份主服务器的数据，将转储文件装载到从服务器# zcat yourdb.sql.gz | mysql -u root -p yourdb14. 启动 Slave 复制进程15. 16. mysql start slave;17. 18. 解除 Master 表锁定19. 20. mysql UNLOCK TABLES;21. MyIASM引擎可以采用下面方法备份数据库# tar zcvf mysql-snapshot.tar.gz /var/lib/mysql/neo复制给从数据库scp mysql-snapshot.tar.gz neo31:/tmpsnapshot 恢复$ tar zxvf mysql-snapshot.tar.gz$ cd /var/lib/mysql$ mv /tmp/var/lib/mysql/neo .$ sudo chown mysql.mysql -R neo重新启动Mysql$ sudo /etc/init.d/mysql restart有兴趣可以看看mysqlhotcopy5.1.5.主从复制安全问题复制帐号权限grant replication slave on *.* to replication192.168.1.% identified by 000000;主库数据库操作帐号权限grant DELETE, INSERT, SELECT, UPDATE ON your_user.* to yourdbyour_host identified by password with grant option;从库数据库操作帐号权限grant SELECT ON your_user.* to yourdbyour_host identified by password with grant option;从库必须收回写操作5.1.6.主从不同步问题执行下面语句stop slave;set global sql_slave_skip_counter =1 ;start slave;Seconds_Behind_Master 值从NULL变为大于等于0是表示已经同步Seconds_Behind_Master: NULLSeconds_Behind_Master: 88935.2.Master Master(主主)5.2.1.Master Af 文件加入下面的内容cp /etc/mysql/f /etc/mysql/f.oldvim /etc/mysql/fmysqldserver-id = 1log-bin=/data/mysql/binlog/binlogbinlog-do-db = testbinlog-ignore-db=mysqllog-slave-updatessync_binlog=1auto_increment_offset=1auto_increment_increment=2replicate-do-db = testreplicate-ignore-db = mysql,information_schema创建复制权限grant replication slave on *.* to replication192.168.1.% identified by 000000;flush privileges;mysqlflush tables with read lock;mysql show master statusG* 1. row *File: binlog.000007Position: 107Binlog_Do_DB: testBinlog_Ignore_DB: mysql1 row in set (0.00 sec)5.2.2.Master B创建复制权限grant replication slave on *.* to replication192.168.1.% identified by 000000;flush privileges;f 文件加入下面的内容mysqldserver-id = 2log-bin = /data/mysql/binlog/binlogreplicate-do-db = testreplicate-ignore-db = mysql,information_schemabinlog-do-db = testbinlog-ignore-db=mysqllog-slave-updatessync_binlog=1auto_increment_offset=2auto_increment_increment=2B 与 A 配置文件不同的两处。server-id = 2auto_increment_offset=2mysql show master statusG* 1. row *File: binlog.000005Position: 107Binlog_Do_DB: testBinlog_Ignore_DB: mysql1 row in set (0.00 sec)5.2.3.将Master A 数据库 同步到 Master B 两端数据库内容保持一致Master A，首先锁表为只读状态mysqldump -databases test /tmp/test.sqlMaster B 创建一个与Master A同名的空数据库,然后将备份文件恢复到数据库中# mysqlmysql CREATE DATABASE test;mysqlq# scp :/tmp/test.sql ./# mysql -uroot -p test change master to master_host=, master_user=replication, master_password=000000, master_log_file=binlog.000005, master_log_pos=107;master-Bmysqlchange master to master_host=, master_user=replication, master_password=000000, master_log_file=binlog.000007, master_log_pos=107;5.2.5.Master A 数据库解除只读权限Master A 解锁mysql UNLOCK TABLES;5.2.6.查看主主的工作状态分别在Master A与B 上运行mysqlshow slave statusG;Slave_IO_Running: YesSlave_SQL_Running: Yes5.3.与复制有关的问题5.3.1.expire-logs-days缺省expire-logs-days为30天。这里设为7天，可根据自己情况调整。mysqldexpire-logs-days = 75.3.2.Semisynchronous Replicationmysql SHOW VARIABLES LIKE have_dynamic_loading;+-+-+| Variable_name | Value |+-+-+| have_dynamic_loading | YES |+-+-+1 row in set (0.00 sec)mysqlmaster INSTALL PLUGIN rpl_semi_sync_master SONAME libsemisync_master.so;slave-x INSTALL PLUGIN