Mysql5.5replication多数据库主从备份Master-Slave配置总结_MySQL

mysqldump -R -E -uroot -p ADB > masteradb.sql;mysqldump -R -E -uroot -p BDB > masterbdb.sql;

GRANT ALL PRIVILEGES ON ADB.* TO usera@`%` IDENTIFIED BY 'pass' WITH GRANT OPTION;GRANT ALL PRIVILEGES ON ADB.* TO usera@`localhost` IDENTIFIED BY 'pass' WITH GRANT OPTION;GRANT ALL PRIVILEGES ON BDB.* TO userb@`%` IDENTIFIED BY 'pass' WITH GRANT OPTION;GRANT ALL PRIVILEGES ON BDB.* TO userb@`localhost` IDENTIFIED BY 'pass' WITH GRANT OPTION;flush privileges;

grant SUPER ON *.* TO 'auser'@'%' IDENTIFIED BY 'pass' WITH GRANT OPTION;grant SUPER ON *.* TO 'buser'@'%' IDENTIFIED BY 'pass' WITH GRANT OPTION;GRANT SELECT ON mysql.proc TO 'auser'@'%' IDENTIFIED BY 'pass' WITH GRANT OPTION;GRANT SELECT ON mysql.proc TO 'buser'@'%' IDENTIFIED BY 'pass' WITH GRANT OPTION;GRANT REPLICATION SLAVE ON *.* TO repluser IDENTIFIED BY 'pass';flush privileges;

mysql> show master status;+------------------+----------+--------------------------------+------------------+| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |+------------------+----------+--------------------------------+------------------+| mysql-bin.000001 | 000173| ADB,BDB | |+------------------+----------+--------------------------------+------------------+1 row in set (0.02 sec)

CHANGE MASTER TO MASTER_HOST='10.224.106.225', MASTER_USER='repluser', MASTER_PASSWORD='pass', master_log_file='mysql-bin.000001', master_log_pos=173;start slave;

SET GLOBAL SQL_SLAVE_SKIP_COUNTER =100;start slave;show slave status/G;stop slave;SET GLOBAL SQL_SLAVE_SKIP_COUNTER =0;start slave;

[plain] [client] port = 3306 socket = /var/lib/mysql/mysql.sock [mysqld] port = 3306 socket = /var/lib/mysql/mysql.sock skip-external-locking key_buffer = 384M max_allowed_packet = 32M table_open_cache = 5120 key_buffer_size = 64M sort_buffer_size = 512M net_buffer_length = 8K read_buffer_size = 16M read_rnd_buffer_size = 128M myisam_sort_buffer_size = 128M query_cache_size = 256M tmp_table_size = 128M max_heap_table_size = 128M thread_concurrency = 8 max_connections=500 group_concat_max_len=1048576 max_sp_recursion_depth=255 slow_query_log_file=/spare/mysql/slow_queries.log long_query_time = 5 binlog_cache_size = 2M join_buffer_size = 32M thread_cache_size = 16 query_cache_limit = 2M transaction_isolation = REPEATABLE-READ log-bin=mysql-bin auto_increment_increment = 2 auto_increment_offset = 1 server-id = 1 binlog-do-db = ADB binlog-do-db = BDB log_slave_updates = 1 relay-log=RELAY_LOCALHOST-relay-bin innodb_data_home_dir = /spare/mysql/ innodb_data_file_path = ibdata1:2000M;ibdata2:10M:autoextend innodb_log_group_home_dir = /spare/mysql/ innodb_additional_mem_pool_size = 16M innodb_buffer_pool_size = 8G innodb_thread_concurrency = 16 innodb_flush_log_at_trx_commit = 2 innodb_log_buffer_size = 8M innodb_log_file_size = 250M max_binlog_size=100M expire_logs_days=3 sync_binlog=1 #Ensure all writes to binary are flushed to disk in a timely manner binlog-format=ROW [mysqldump] quick max_allowed_packet = 256M [mysql] no-auto-rehash [isamchk] key_buffer = 256M sort_buffer_size = 256M read_buffer = 2M write_buffer = 2M [myisamchk] key_buffer = 256M sort_buffer_size = 256M read_buffer = 2M write_buffer = 2M [mysqlhotcopy] interactive-timeout slave my.cnf配置[plain] [client] port = 3306 socket = /var/lib/mysql/mysql.sock [mysqld] port = 3306 socket = /var/lib/mysql/mysql.sock skip-external-locking key_buffer = 384M max_allowed_packet = 32M table_open_cache = 5120 key_buffer_size = 64M sort_buffer_size = 512M net_buffer_length = 8K read_buffer_size = 16M read_rnd_buffer_size = 128M myisam_sort_buffer_size = 128M query_cache_size = 256M tmp_table_size = 128M max_heap_table_size = 128M thread_concurrency = 8 max_connections=500 group_concat_max_len=1048576 max_sp_recursion_depth=255 slow_query_log_file=/spare/mysql/slow_queries.log long_query_time = 5 binlog_cache_size = 2M join_buffer_size = 32M thread_cache_size = 16 query_cache_limit = 2M transaction_isolation = REPEATABLE-READ log-bin=mysql-bin auto_increment_increment = 2 auto_increment_offset = 1 # the slave offset keep same as master server-id = 2 replicate-do-db = ADB replicate-do-db = BDB log_slave_updates = 1 relay-log=RELAY_LOCALHOST-relay-bin innodb_data_home_dir = /spare/mysql/ innodb_data_file_path = ibdata1:2000M;ibdata2:10M:autoextend innodb_log_group_home_dir = /spare/mysql/ innodb_additional_mem_pool_size = 16M innodb_autoextend_increment = 256M innodb_buffer_pool_size = 8G innodb_thread_concurrency = 16 innodb_flush_log_at_trx_commit = 2 innodb_log_buffer_size = 8M ####innodb_log_buffer_size = 32M innodb_log_file_size = 250M max_binlog_size=100M expire_logs_days=3 sync_binlog=1 #Ensure all writes to binary are flushed to disk in a timely manner binlog-format=ROW [mysqldump] quick max_allowed_packet = 256M [mysql] no-auto-rehash [isamchk] key_buffer = 256M sort_buffer_size = 256M read_buffer = 2M write_buffer = 2M [myisamchk] key_buffer = 256M sort_buffer_size = 256M read_buffer = 2M write_buffer = 2M [mysqlhotcopy] interactive-timeout