Mysql5.5replication multi-database Master-Slave backup Master-Slave configuration summary _ MySQL

Mysql5.5replication multi-database Master-Slave backup Master-Slave configuration summary bitsCN.com

Mysql 5.5 replication multi-database Master-Slave backup Master-Slave configuration summary

Configure dual-Machine backup for Mysql server 5.5, that is, master-slave mode. In this example, multiple databases are copied.

Currently, two databases are on the same mysql server, that is, the master. each database has its own user access and operations for different applications. Both databases must be copied to another mysql server in real time through replication configuration, that is, slave.

Configuration steps:

1. existing data everywhere from the master

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

2. customize the configuration file/etc/my. cnf for the master and slave, and then start mysql

Vi/etc/my. cnf

### For details, see The Appendix

Service mysql restart

3. create a database on slave.

Create database ADB;

Create database BDB;

4. configure user and privilege on master and slave;

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;

5. configure the user and privilege for data replication on the master;

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;

6. run the following command on the master to obtain the master status, including the binlog file name and current location.

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)

7. configure the replication source in slave, including hostname, user, password, binlog file name, and location.

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;

8. run the following command to view the current status of slave.

Show slave status/G

Result

Slave_IO_Running: Yes

Slave_ SQL _Running: Yes

This indicates that the data synchronization and replication operation is successful.

Tip: If an error occurs in show slave status/G, run the following command to skip this step and restart 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;

Appendix:

Master my. cnf configuration

[Plain] [client] port = 3306 socket =/var/lib/mysql. sock [mysqld] port = 3306 socket =/var/lib/mysql. sock skip-external-locking key_buffer = 384 M bytes = 32 M table_open_cache = 5120 key_buffer_size = 64 M sort_buffer_size = 512 M net_buffer_length = 8 K read_buffer_size = 16 M bytes = 128 M bytes M query_cache_size = 256 M tmp_table_size = 128 M bytes = 128 M thread_concurrency = 8 max_connections = 500 bytes = 1048576 bytes = 255 bytes =/spare/mysql/slow_queries.log long_query_time = 5 binlog_cache_size = 2 M rows = 32 M thread_cache_size = 16 query_cache_limit = 2 M transaction_isolation = REPEATABLE-READ log-bin = mysql-bin rows = 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: 2000 M; ibdata2: 10 M: autoextend records =/spare/mysql/records = 16 M records = 8G records = 16 bytes = 2 bytes = 8 M innodb_log_file_size = 250 M max_binlog_size = 100 M 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 = 256 M [mysql] no-auto-rehash [isamchk] key_buffer = 256 M sort_buffer_size = 256 M read_buffer = 2 M write_buffer = 2 M [myisamchk] key_buffer = 256 M sort_buffer_size = 256 M read_buffer = 2 M write_buffer = 2 M [mysqlhotcopy] interactive-timeout slave my. cnf configuration [plain] [client] port = 3306 socket =/var/lib/mysql. sock [mysqld] port = 3306 socket =/var/lib/mysql. sock skip-external-locking key_buffer = 384 M bytes = 32 M table_open_cache = 5120 key_buffer_size = 64 M sort_buffer_size = 512 M net_buffer_length = 8 K read_buffer_size = 16 M bytes = 128 M bytes M query_cache_size = 256 M tmp_table_size = 128 M bytes = 128 M thread_concurrency = 8 max_connections = 500 bytes = 1048576 bytes = 255 bytes =/spare/mysql/slow_queries.log long_query_time = 5 binlog_cache_size = 2 M rows = 32 M thread_cache_size = 16 query_cache_limit = 2 M transaction_isolation = REPEATABLE-READ log-bin = mysql-bin rows = 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: 2000 M; ibdata2: 10 M: autoextend memory =/spare/mysql/memory = 16 M memory = 256 M memory = 8G innodb_thread_concurrency = 16 memory = 2 innodb_log_buffer_size = 8 M #### memory = 32 M innodb_log_file_size = 250 M max_binlog_size = 100 M bytes = 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 = 256 M [mysql] no-auto-rehash [isamchk] key_buffer = 256 M sort_buffer_size = 256 M read_buffer = 2 M write_buffer = 2 M [myisamchk] key_buffer = 256 M sort_buffer_size = 256 M read_buffer = 2 M write_buffer = 2 M [mysqlhotcopy] interactive-timeout

BitsCN.com