Set up dual-machine hot standby:
The first thing to do is to set up a synchronization user on two machines:
Grant Replication Slave on * * to ' Repdcs ' @ ' 192.168.21.39 ' identified by ' 123456 ';
Grant all privileges on * * to ' Repdcs ' @ ' 192.168.21.39 identified by ' 123456 ';
FLUSH privileges;
Grant Replication Slave on * * to ' Repdcs ' @ ' 192.168.21.106 ' identified by ' 123456 ';
Grant all privileges on * * to ' Repdcs ' @ ' 192.168.21.106 identified by ' 123456 ';
FLUSH privileges;
Library 1
[Mysqld]
Datadir=/var/lib/mysql
Socket=/var/lib/mysql/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_errors = 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
Library 2
[Mysqld]
#datadir =/var/lib/mysql
Datadir=/home/data/mysql
#socket =/var/lib/mysql/mysql.sock
Socket=/home/data/mysql/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 does so, uncomment the 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 = 8M
Max_allowed_packet = 4M
Sort_buffer_size = 6M
Read_buffer_size = 4M
Join_buffer_size = 4M
Myisam_sort_buffer_size = 64M
Thread_cache_size = 64
Query_cache_size = 0M
Tmp_table_size = 96M
Max_connections = 500
table_cache= 1024
Innodb_additional_mem_pool_size= 16M
Innodb_log_buffer_size= 64M
Read_rnd_buffer_size= 16M
Innodb_buffer_pool_size= 1G
Innodb_log_file_size = 256M
Max_heap_table_size = 96M
Innodb_data_file_path = Ibdata1:200m:autoextend
Default-storage-engine=innodb
Max_connect_errors = 100000
Long_query_time = 1
[Mysqld_safe]
Log-error=/var/log/mysqld.log
Pid-file=/var/run/mysqld/mysqld.pid
[Client]
Default-character-set=utf8
The red words are mainly two different parts of the machine.
Reboot if the problem
Show slave status \g
Last_error:error ' Can ' t create database ' DCS '; Database exists ' on query. Default database: ' DCs '. Query: ' CR
The query from the library to find the database that needs to be established already exists, so you can skip the command to build the library.
Use set global sql_slave_skip_counter=1;
Start slave sql_thread;
Flush Privileges
Server-id=n//Set database ID The default master server is 1 and can be set casually but cannot be duplicated if there are multiple slave servers.
IP address or domain name of the master-host=192.168.21.39//master server
master-port=3306//port number of the primary database
Master-user=repdcs//Synchronizing the database user
master-password=123456//Synchronizing the password of the database
MASTER-CONNECT-RETRY=60//If the primary server is found to be disconnected from the server, the time lag for reconnection
report-host=db-slave.mycompany.com//reporting the wrong server
Then restart the database of two machines, basically no problem, the approximate steps to the master and subordinate, you can refer to my blog MySQL master-slave parameter configuration and steps
MySQL dual-Machine hot standby configuration steps
