MYSQL双机热备配置(主-主)
MYSQL双机热备配置(主-主)
-
安装MYSQL
省略
修改root密码:
UPDATE user SET password=PASSWORD('utl413') WHERE user='root';
FLUSH PRIVILEGES;
-
分别使用root用户登录mysql
创建需要同步的数据库(略)
创建需要同步的用户:
CREATE USER 'cbc1'@'%' IDENTIFIED BY 'cbc123';
grant all privileges on CBC.* to [email protected]'%';
flush privileges;
分配同步权限
GRANT REPLICATION SLAVE,REPLICATION CLIENT,RELOAD,SUPER ON *.* TO 'cbc1'@'192.168.1.111' IDENTIFIED BY 'cbc123';
GRANT REPLICATION SLAVE,REPLICATION CLIENT,RELOAD,SUPER ON *.* TO 'cbc1'@'192.168.1.154' IDENTIFIED BY 'cbc123';
-
配置my.cnf 文件
[mysqld]
datadir=/data/mysql
socket=/data/mysql/mysql.sock
user=mysql
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
#skip-grant-tables
character-set-server=utf8
collation-server=utf8_general_ci
lower_case_table_names = 0
server-id=1
log-bin=/var/log/mysql/mysql-bin.log
binlog-do-db=cbc
replicate-do-db=cbc
binlog-ignore-db=mysql
lower_case_table_names=1
log-slave-updates
sync_binlog=1
auto_increment_offset=1
auto_increment_increment=2
replicate-ignore-db=mysql,information_schema
[client]
port = 3306
socket = /data/mysql/mysql.sock
default-character-set=utf8
[mysql]
socket=/data/mysql/mysql.sock
[mysqladmin]
socket=/data/mysql/mysql.sock
[mysqldump]
socket=/data/mysql/mysql.sock
[mysqld_safe]
socket=/data/mysql/mysql.sock
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
Server B:
[mysqld]
datadir=/data/mysql
socket=/data/mysql/mysql.sock
user=mysql
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
#skip-grant-tables
character-set-server=utf8
collation-server=utf8_general_ci
lower_case_table_names = 0
server-id=2
log-bin=/var/log/mysql/mysql-bin.log
binlog-do-db=cbc
replicate-do-db=cbc
binlog-ignore-db=mysql
replicate-ignore-db=mysql,information_schema,performance_schema
log-slave-updates
sync_binlog=1
auto_increment_offset=2
auto_increment_increment=2
[client]
port = 3306
socket = /data/mysql/mysql.sock
default-character-set=utf8
[mysql]
socket=/data/mysql/mysql.sock
[mysqladmin]
socket=/data/mysql/mysql.sock
[mysqldump]
socket=/data/mysql/mysql.sock
[mysqld_safe]
socket=/data/mysql/mysql.sock
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
- 配置完my.cnf文件后,重启两台服务器
-
各自设置主从关系
登陆主服务器:
Show slave status\G
登陆从服务器
执行 stop slave
设置主服务器关系
change master to
master_host='172.0.0.200',master_user='cbcsyn',master_password='[email protected]',master_log_file='mysql-bin.000013',master_log_pos=106;
注意master_log_file 要对应 主服务器状态显示的File
Master_log_pos对应从主服务器的 Position