mariadb主从复制及互为主从配置过程
实验环境:两台电脑一台的ip地址为192.168.153.133 一台为192.168.153.133
主从复制
1.先安装mariadb数据库
yum install mariadb-server
2.修改配置文件/etc/my.conf增加如下代码
主服务器:
server-id= 160 //server-id 必须唯一,
log-bin=mysql-bin //开启二进制日志
# binlog-do-db = testdb //只记录testdb库变化,多个库用‘,’分隔
# binlog-ignore-db=mysql //忽略mysql库变化,多个库用‘,’分隔
从服务器:
server-id= 162 //server-id 必须唯一,
log-bin=mysql-bin //开启二进制日志
#log-slave-updates //当做级联复制,或者从库做备份时A-->B-->C,B服务需要开启log-bin和log-slave-updates,二者缺一不可
# expire_logs_days =7 //当binlog日志较多时,此参数的值意思是只保留7天内的数据。主库及从库都可以配置此参数。
# replication-do-db = testdb //只同步testdb库,多个库用‘,’分隔
# replication-ignore-db=mysql //不同步mysql库,多个库用‘,’分隔
# replication-do-table = test_tb 只同步test_tb表,多个表用‘,’分隔
# replication-ignore-table = test_tb 不同步test_tb表,多个表用‘,’分隔
# replication-wild-do-table = test_tb //同replication-do-table一样,可以加通配符
# replication-wild-ignore-table = test_tb //同replication-ignore-table一样,可以加通配符
3.修改完成后重启mariadb服务。
systemctl restart mariadb
4.在主服务器上建立帐户并授权slave
mysql>GRANT REPLICATION SLAVE ON *.* TO sqlsync@192.168.153.134 IDENTIFIED BY '12345678';
mysql>FLUSH PRIVILEGES;
5.此时不能有数据写入数据库。
执行以下命令锁定数据库以防止写入数据
mysql>FLUSH TABLES WITH READ LOCK;
6.登录主服务器的mysql,查询master的状态
mysql>show master status;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 | 517 | | |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
注:执行完此步骤后不要再操作主服务器MYSQL,防止主服务器状态值变化
解锁数据表。
mysql>UNLOCK TABLES;
7、配置从服务器Slave:
mysql>change master to master_host='192.168.153.133',master_user='sqlsync',master_password='12345678',master_log_file='mysql-bin.000001',master_log_pos=517;
Mysql>start slave; //启动从服务器复制功能
8、检查从服务器复制功能状态:
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 10.33.133.160 //主服务器地址
Master_User: sqlsync //授权帐户名,尽量避免使用root
Master_Port: 3306 //数据库端口,部分版本没有此行
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 822 //#同步读取二进制日志的位置,大于等于Exec_Master_Log_Pos
Relay_Log_File: ddte-relay-bin.000001
Relay_Log_Pos: 251
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: Yes //此状态必须YES
Slave_SQL_Running: Yes //此状态必须YES
......
注:Slave_IO及Slave_SQL进程必须正常运行,即YES状态,否则都是错误的状态(如:其中一个NO均属错误)。
至此我们mysql服务器的主从复制架构已经完成,但是我们现在的主从架构并不完善,因为我们的从服务上还可以进行数据库的写入操作,一旦用户把数据写入到从服务器的数据库内,
然后从服务器从主服务器上同步数据库的时候,会造成数据的错乱,从而会造成数据的损坏,所以我们需要把从服务器设置成只读
方法:
mysql> SHOW global variables like 'read%'; //查看read_only 状态
+----------------------+--------+
| Variable_name | Value |
+----------------------+--------+
| read_buffer_size | 131072 |
| read_only | OFF |
| read_rnd_buffer_size | 262144 |
+----------------------+--------+
rows in set (0.01 sec)
mysql> SET GLOBAL read_only=1; //此为全局变量,只有管理员才有权限配置。设置read_only为只读,当前环境生效,重启后失效。
vim /etc/my.cnf 添加一行read_only=1 //让此项全局有效。
注意:read-only = ON ,这项功能只对非管理员组以为的用户有效!
配置主<—>主同步。
1.主服务器:(此处主服务器是上面主从复制的主服务器,已配置好server-id故不需要配置此项)
log-bin=mysql-bin
relay-log = relay-mysql //开启二进制日志
relay-log-index =relay-mysql.index //开启中继日志
auto-increment-increment = 1 //每次跳两个数
auto-increment-offset = 1 //从一开始
2.从服务器:
log-bin=mysql-bin
server-id= 134 //server-id 必须唯一,
relay-log = relay-mysql //开启二进制日志
relay-log-index =relay-mysql.index //开启中继日志
auto-increment-increment = 1
auto-increment-offset = 1
3.配置完成后重启服务。
4.此时两台服务器均为新建立,且无其它写入操作,各服务器只需记录当前自己二进制日志文件及事件位置,以之作为另外的服务器复制起始位置即可
#master
MariaDB [(none)]> show master status;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000002 | 245 | | |
+------------------+----------+--------------+------------------+
1 row in set (0.01 sec)
#slave
MariaDB [(none)]> show master status;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000004 | 245 | | |
+------------------+----------+--------------+------------------+
1 row in set (0.01 sec)
5.两台服务器上分别创建账户和权限,来进行同步设置。
#master
mysql>GRANT REPLICATION SLAVE ON *.* TO sqlsync@192.168.153.134 IDENTIFIED BY '12345678';
#slave
mysql>GRANT REPLICATION SLAVE ON *.* TO sqlsync@192.168.153.133 IDENTIFIED BY '12345678';
6.各服务器上指定对另一台服务器为自己的主服务器:
#master
mysql>change master to master_host='192.168.153.134',master_user='sqlsync',master_password='12345678',master_log_file='mysql-bin.000002',master_log_pos=245;
#slave
change master to master_host='192.168.153.133',master_user='sqlsync',master_password='12345678',master_log_file='mysql-bin.000002',master_log_pos=245;
7.启动复制进程;
mysql>start slave;
8、检查从服务器复制功能状态:
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 10.33.133.160 //主服务器地址
Master_User: sqlsync //授权帐户名,尽量避免使用root
Master_Port: 3306 //数据库端口,部分版本没有此行
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 245 //#同步读取二进制日志的位置,大于等于Exec_Master_Log_Pos
Relay_Log_File: ddte-relay-bin.000004
Relay_Log_Pos: 245
Relay_Master_Log_File: mysql-bin.000002
Slave_IO_Running: Yes //此状态必须YES
Slave_SQL_Running: Yes //此状态必须YES
以上主到主配置完成。
配置中遇到的错误总结:
在配置主<–>主的过程中,因在从服务器之前配置过主–>从的模式,导致start slave无法启动。查看mysql错误日志。
报错Failed to open the relay log ‘./mysqld-relay-bin.000025’ (relay_log_pos 251)。
到mysql主服务器配置文件/var/lib/mysql/master.info
修改之前配置的master_log_file和master_log_pos值为现在主服务器上的值。