Heim > Datenbank > MySQL-Tutorial > mysql双机热备的配置步骤_MySQL

mysql双机热备的配置步骤_MySQL

WBOY
Freigeben: 2016-06-01 13:04:05
Original
1037 Leute haben es durchsucht

设置双机热备:

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

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默认主服务器是1可以随便设置但是如果有多台从服务器则不能重复。
master-host=192.168.21.39 //主服务器的IP地址或者域名
master-port=3306 //主数据库的端口号
master-user=repdcs //同步数据库的用户
master-password=123456 //同步数据库的密码
master-connect-retry=60 //如果从服务器发现主服务器断掉,重新连接的时间差

report-host=db-slave.mycompany.com //报告错误的服务器

然后重启两个机器的数据库,基本就没问题了,大致步骤给主从差不多,可以参考我的博客 mysql主从的参数配置与步骤

Verwandte Etiketten:
Quelle:php.cn
Erklärung dieser Website
Der Inhalt dieses Artikels wird freiwillig von Internetnutzern beigesteuert und das Urheberrecht liegt beim ursprünglichen Autor. Diese Website übernimmt keine entsprechende rechtliche Verantwortung. Wenn Sie Inhalte finden, bei denen der Verdacht eines Plagiats oder einer Rechtsverletzung besteht, wenden Sie sich bitte an admin@php.cn
Beliebte Tutorials
Mehr>
Neueste Downloads
Mehr>
Web-Effekte
Quellcode der Website
Website-Materialien
Frontend-Vorlage