Configuration steps for mysql dual-machine hot standby
Set dual-machine Hot Standby:
First, you must create a synchronization user on the two machines:
Grant replication slave on *. * to 'repdcs '@ '192. 168.21.39' identified by '123 ';
Grant all privileges on *. * to 'repdcs '@ '192. 168.21.39 identified by '123 ';
Flush privileges;
Grant replication slave on *. * to 'repdcs '@ '192. 168.21.106 'identified by '123 ';
Grant all privileges on *. * to 'repdcs '@ '192. 168.21.106 identified by '123 ';
Flush privileges;
Database 1
[Mysqld]
Datadir =/var/lib/mysql
Socket =/var/lib/mysql. sock
User = mysql
Port = 3306
# Default to using old password format for compatibility with mysql 3.x
# Clients (those using the mysqlclient10 compatibility package ).
Old_passwords = 1
Lower_case_table_names = 1
Default-character-set = utf8
Default-storage-engine = innodb
Max_connect_errorrs = 100000
Innodb_buffer_pool_size = 8G
Max_connections = 500
Default-character-set = utf8
Server-id = 2
# Log-bin = mysqlbin
Innodb_flush_log_at_trx_commit = 1
Sync_binlog = 1
Init_connect = 'set NAMES utf8'
Log-bin = mysqlbin
Master-host = 192.168.21.39
Master-user = repdcs
Master-pass = 123456
Master-connect-retry = 60
Replicate-do-db = dcs
Master-port = 3306
Slave-net-timeout = 60
Database 2
[Mysqld]
# Datadir =/var/lib/mysql
Datadir =/home/data/mysql
# Socket =/var/lib/mysql. sock
Socket =/home/data/mysql. sock
User = mysql
Port = 3306
# Default to using old password format for compatibility with mysql 3.x
# Clients (those using the mysqlclient10 compatibility package ).
Old_passwords = 1
Default-character-set = utf8
Init_connect = 'set NAMES utf8'
# Disabling symbolic-links is recommended to prevent assorted security risks;
# To do so, uncomment this line:
# Symbolic-links = 0
Server-id = 1
Log-bin = mysqlbin
Innodb_flush_log_at_trx_commit = 1
Sync_binlog = 1
Init_connect = 'set NAMES utf8'
Log-bin = mysqlbin
Master-host = 192.168.21.106
Master-user = repdcs
Master-pass = 123456
Master-connect-retry = 60
Replicate-do-db = dcs
Master-port = 3306
Slave-net-timeout = 60
# Replicate-do-db = dcs
Back_log = 512
Key_buffer_size = 8 M
Max_allowed_packet = 4 M
Sort_buffer_size = 6 M
Read_buffer_size = 4 M
Join_buffer_size = 4 M
Myisam_sort_buffer_size = 64 M
Thread_cache_size = 64
Query_cache_size = 0 M
Tmp_table_size = 96 M
Max_connections = 500
Table_cache = 1024
Innodb_additional_mem_pool_size = 16 M
Innodb_log_buffer_size = 64 M
Read_rnd_buffer_size = 16 M
Innodb_buffer_pool_size = 1G
Innodb_log_file_size = 256 M
Max_heap_table_size = 96 M
Innodb_data_file_path = ibdata1: 200 M: autoextend
Default-storage-engine = innodb
Max_connect_errorrs = 100000
Long_query_time = 1
[Mysqld_safe]
Log-error =/var/log/mysqld. log
Pid-file =/var/run/mysqld. pid
[Client]
Default-character-set = utf8
If it is marked as red, it mainly involves different parts of the two machines.
Restart if something goes wrong
Show slave status \ G
Last_Error: Error 'can't create database 'dcs '; database exists' on query. Default database: 'dcs '. Query: 'cr
Query the slave database and find that the database to be created already exists. Therefore, you can skip the database creation command.
Use set global SQL _slave_skip_counter = 1;
Start slave SQL _thread;
Flush privileges
Server-id = n // set the database id. The default master server is 1, which can be set at will, but it cannot be repeated if there are multiple slave servers.
Master-host = 192.168.21.39 // ip address or domain name of the master server
Master-port = 3306 // The port number of the master database
Master-user = repdcs // user who synchronizes the database
Master-password = 123456 // password for Database Synchronization
Master-connect-retry = 60 // time difference between the reconnection if the master server is disconnected from the slave server
Report-host = db-slave.mycompany.com // server that reports errors
Then restart the databases of the two machines. The general steps are similar to those of the master and slave databases. For details, refer to mysql master and slave parameter configuration and steps in my blog.
