1.前期准备
准备两台服务器:
第一台:10.0.212.173 端口号:3306 主服务器
my.cnf配置文件 主要添加了以下配置
[mysqld]
log_bin=linux1
server_id = 1
basedir = /usr/local/mysql
datadir = /usr/local/mysql/data
port = 3306
character-set-server = utf8
skip-name-resolve
log-err = /usr/local/mysql/data/error.log
pid-file = /usr/local/mysql/data/mysql.pid
第二台:10.0.213.1 端口号:3307 从服务器
my.cnf配置文件 主要添加了以下配置
[mysqld]
log_bin=linux2
basedir = /usr/local/mysql
datadir = /usr/local/mysql/data
port = 3307
server_id = 2
socket = /tmp/mysql.sock
character-set-server = utf8
skip-name-resolve
log-err = /usr/local/mysql/data/error.log
pid-file = /usr/local/mysql/data/mysql.pid
把防火墙关闭,或者防火墙配置允许相应的端口访问,以下防火墙的配置文件需要安装iptable,具体命令可以网上搜索以下。
例如: vi /etc/sysconfig/iptables 在端口3306的防火墙配置文件中添加
-A INPUT -m state --state NEW -m tcp -p tcp --dport 3306-j ACCEPT
在端口3307的防火墙配置中添加
-A INPUT -m state --state NEW -m tcp -p tcp --dport 3307-j ACCEPT
2.创建授权账号
在主服务器执行命令:
GRANT FILE ON *.* TO 'repl'@'10.0.213.1' IDENTIFIED BY 'repl';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'10.0.213.1' IDENTIFIED BY 'repl';
flush privileges;
在从服务器执行命令:
GRANT FILE ON *.* TO 'repl'@'10.0.212.173' IDENTIFIED BY 'repl';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'10.0.212.173' IDENTIFIED BY 'repl';
flush privileges;
不想执行以上两个授权命令直接授权all权限也是可以的。
两台服务器的账号密码可以随意设置。配置双机热备其实就是两台服务器相互配置主从复制。
IP地址设置为对应服务器的ip,应该也可以设置为%,还没测试过。
以上操作结束后
3.配置数据库
先从主服务器开始
root登录,执行
mysql> show master status;
查看当前主数据库状态:
记录当前的file与position的值;
进入从服务器访问数据库查看其主数据库状态,也是用root登录操作
记录当前的file与position的值;
在从服务器执行
CHANGE MASTER TO MASTER_HOST='10.0.212.173',MASTER_USER='repl',MASTER_PASSWORD='repl',MASTER_PORT=3306,MASTER_LOG_FILE='linux1.000022',MASTER_LOG_POS=1664;
注:参数顺序分别为主服务器ip,用户名,密码,端口号,查询主库状态的file和position;
注:若slave开启状态无法执行以上命令,需要首先执行 stop slave;关闭slave,执行完上述命令后执行start slave;命令开启slave。
执行完毕:查看从服务状态 主服务器从服务器-主从复制
执行下面的操作
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 10.0.212.173
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: linux1.000022
Read_Master_Log_Pos: 2836
Relay_Log_File: mysql-relay-bin.000006
Relay_Log_Pos: 280
Relay_Master_Log_File: linux1.000022
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 2836
Relay_Log_Space: 988
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1
Master_UUID: b61f0c81-a929-11e9-b1f0-000c29110fb1
Master_Info_File: /usr/local/mysql/data/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
1 row in set (0.00 sec)
执行完毕:查看从服务状态 从服务器主服务器-主从复制
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 10.0.213.1
Master_User: repl
Master_Port: 3307
Connect_Retry: 60
Master_Log_File: linux2.000031
Read_Master_Log_Pos: 554
Relay_Log_File: mysql-relay-bin.000002
Relay_Log_Pos: 714
Relay_Master_Log_File: linux2.000031
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 554
Relay_Log_Space: 887
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 2
Master_UUID: 7e23f390-a97d-11e9-b412-000c29cbe6df
Master_Info_File: /usr/local/mysql/data/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
1 row in set (0.00 sec)
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
两者都为yes则配置成功。在主服务器数据库插入数据,在从服务器使用授权的账号登录查看数据是否同步。到此完成主从配置。
主从复制是从主服务器同步数据到从服务器机,反向配置一次从从服务器同步数据到主服务器即可完成双机热备-同步复制更新。。步骤与上面的一致。双机热备成功不管在主服务器数据库更新数据还是在从服务器数据库更新数据两台服务器的数据都能同步更新。
关于遇到的以上两者不全为Yes的可以自行百度解决。
存在不足之处,请多多指正。