mysql双机热备的配置步骤

设置双机热备：

首先要在两台机器上建立同步用户：

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;

库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
库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 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 = 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

标红的话主要两台机器的不同部分。

重新启动假设出问题
show slave status \G
Last_Error: Error 'Can't create database 'dcs'; database exists' on query. Default database: 'dcs'. Query: 'cr
查询从库发现须要建立的数据库已经存在,所以能够跳过这个建库的命令.
使用set global sql_slave_skip_counter=1;
start slave sql_thread;
flush privileges

server-id=n //设置数据库id默认主server是1能够随便设置可是假设有多台从server则不能反复。

master-host=192.168.21.39 //主server的IP地址或者域名
master-port=3306 //主数据库的端口号
master-user=repdcs //同步数据库的用户
master-password=123456 //同步数据库的密码
master-connect-retry=60 //假设从server发现主server断掉，又一次连接的时间差

report-host=db-slave.mycompany.com //报告错误的server

然后重新启动两个机器的数据库，基本就没问题了，大致步骤给主从差点儿相同，能够參考我的博客 mysql主从的參数配置与步骤

转载于:https://www.cnblogs.com/mfrbuaa/p/5155465.html