阿里云mysql双机热备_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/mysqlsocket=/var/lib/mysql/mysql.sockuser=mysqlport=3306# Default to using old password format for compatibility with mysql 3.x# clients (those using the mysqlclient10 compatibility package).old_passwords=1lower_case_table_names=1default-character-set=utf8default-storage-engine=innodbmax_connect_errors = 100000innodb_buffer_pool_size= 8Gmax_connections = 500default-character-set=utf8server-id=2#log-bin=mysqlbininnodb_flush_log_at_trx_commit=1sync_binlog=1init_connect='SET NAMES utf8'log-bin=mysqlbinmaster-host=192.168.21.39

master-user=repdcs

master-pass=123456

master-connect-retry=60

replicate-do-db=dcsmaster-port=3306slave-net-timeout=60库2[mysqld]#datadir=/var/lib/mysqldatadir=/home/data/mysql#socket=/var/lib/mysql/mysql.socksocket=/home/data/mysql/mysql.sockuser=mysqlport=3306# Default to using old password format for compatibility with mysql 3.x# clients (those using the mysqlclient10 compatibility package).old_passwords=1default-character-set=utf8init_connect = 'SET NAMES utf8'# Disabling symbolic-links is recommended to prevent assorted security risks;# to do so, uncomment this line:# symbolic-links=0server-id=1log-bin=mysqlbininnodb_flush_log_at_trx_commit=1sync_binlog=1init_connect='SET NAMES utf8'log-bin=mysqlbinmaster-host=192.168.21.106

master-user=repdcs

master-pass=123456

master-connect-retry=60

replicate-do-db=dcs

master-port=3306slave-net-timeout=60#replicate-do-db=dcsback_log = 512key_buffer_size = 8Mmax_allowed_packet = 4Msort_buffer_size = 6Mread_buffer_size = 4Mjoin_buffer_size = 4Mmyisam_sort_buffer_size = 64Mthread_cache_size = 64query_cache_size = 0Mtmp_table_size = 96Mmax_connections = 500table_cache= 1024innodb_additional_mem_pool_size= 16Minnodb_log_buffer_size= 64Mread_rnd_buffer_size= 16Minnodb_buffer_pool_size= 1Ginnodb_log_file_size = 256Mmax_heap_table_size = 96Minnodb_data_file_path = ibdata1:200M:autoextenddefault-storage-engine=innodbmax_connect_errors = 100000long_query_time = 1[mysqld_safe]log-error=/var/log/mysqld.logpid-file=/var/run/mysqld/mysqld.pid[client]default-character-set=utf8标红的话主要两台机器的不同部分。重新启动假设出问题show slave status \GLast_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 privilegesserver-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主从的參数配置与步骤

本文转自mfrbuaa博客园博客，原文链接：http://www.cnblogs.com/mfrbuaa/p/5155465.html，如需转载请自行联系原作者