需要主从复制基础
《MariaDB主从复制》
开放数据库端口
firewall-cmd --zone=public --add-port=3306/tcp --permanent
firewall-cmd --reload
准备工作
停止全部数据库的主从复制!!!需要主从复制的库一个都不能留,备份以后全部删掉!!!
mysql -u root -p
不知道是主库还是从库就都执行
STOP SLAVE;
主从数据库:A——》B是主从,即B是A的从库
从主数据库:A《——B是从主,即A是B的从库
双机热备:A⇋B,A和B互为主从库
主从数据库:192.168.1.11
从主数据库:192.168.1.111
Master主Slave从数据库(主从)
没有/etc/my.cnf就创建,改/etc/my.cnf.d/server.cnf是没用的!!!如果指定了datadir和basedir需要同时在my.cnf和server.cnf配置
/etc/my.cnf
[mysqld]
#主
#id不能重复
server-id=1
#日志名称
log-bin=mariadb-bin
#被复制的库,多个用英文逗号隔开
binlog-do-db=demo
binlog_format=STATEMENT
#从
relay-log=mariaDB-relay
重启主从数据库
systemctl restart mariadb
登录MariaDB查询主从数据库是否启动bin-log日志
mysql -u root -p
show variables like '%log_bin%';
+---------------------------------+----------------------------------+
| Variable_name | Value |
+---------------------------------+----------------------------------+
| log_bin | ON |
| log_bin_basename | /var/lib/mysql/mariadb-bin |
| log_bin_compress | OFF |
| log_bin_compress_min_len | 256 |
| log_bin_index | /var/lib/mysql/mariadb-bin.index |
| log_bin_trust_function_creators | OFF |
| sql_log_bin | ON |
+---------------------------------+----------------------------------+
出现log_bin的lavue是ON代表成功!
查看主从数据库的状态
show master status;
+--------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+--------------------+----------+--------------+------------------+
| mariadb-bin.000001 | 330 | demo | |
+--------------------+----------+--------------+------------------+
file的值后面会用到,Binlog_Do_DB就是需要主从复制的数据库。
Slave从Master主数据库(从主)
没有/etc/my.cnf就创建,改/etc/my.cnf.d/server.cnf是没用的!!!如果指定了datadir和basedir需要同时在my.cnf和server.cnf配置
/etc/my.cnf
[mysqld]
#主
#id不能重复
server-id=2
#日志名称
log-bin=mariadb-bin
#被复制的库,多个用英文逗号隔开
binlog-do-db=demo
binlog_format=STATEMENT
#从
relay-log=mariaDB-relay
重启从主数据库
systemctl restart mariadb
登录MariaDB查询从主数据库是否启动bin-log日志
mysql -u root -p
show variables like '%log_bin%';
+---------------------------------+----------------------------------+
| Variable_name | Value |
+---------------------------------+----------------------------------+
| log_bin | ON |
| log_bin_basename | /var/lib/mysql/mariadb-bin |
| log_bin_compress | OFF |
| log_bin_compress_min_len | 256 |
| log_bin_index | /var/lib/mysql/mariadb-bin.index |
| log_bin_trust_function_creators | OFF |
| sql_log_bin | ON |
+---------------------------------+----------------------------------+
出现log_bin的lavue是ON代表成功!
查看从主数据库的状态
show master status;
+--------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+--------------------+----------+--------------+------------------+
| mariadb-bin.000002 | 333 | demo | |
+--------------------+----------+--------------+------------------+
file的值后面会用到,Binlog_Do_DB就是需要主从复制的数据库。
回到主从数据库A192.168.1.11
A——》B,A是B的从库配置:
MASTER_HOST=主机IP(从主数据库IP地址)
MASTER_USER=主机用户
MASTER_PASSWORD=主机用户密码
MASTER_LOG_FILE=对应上面的File的值
MASTER_LOG_POS=对应上面的Position的值
建议创建一个单独用来主从复制的账号,如:copy
CHANGE MASTER TO MASTER_HOST='192.168.1.111',MASTER_USER='copy',MASTER_PASSWORD='copy',MASTER_LOG_FILE='mariadb-bin.000002',MASTER_LOG_POS=333;
启动主从复制
start slave;
正确结果如下:
[anolis@localhost ~]$ mysql -u root -p
Enter password:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 3
Server version: 10.6.10-MariaDB MariaDB Server
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
#建议创建一个单独用来主从复制的账号,如:copy
MariaDB [(none)]> CHANGE MASTER TO MASTER_HOST='192.168.1.111',MASTER_USER='root',MASTER_PASSWORD='123456',MASTER_LOG_FILE='mariadb-bin.000002',MASTER_LOG_POS=333;
Query OK, 0 rows affected (0.008 sec)
MariaDB [(none)]> start slave;
Query OK, 0 rows affected (0.001 sec)
主从库查看主从复制是否成功
SHOW SLAVE STATUS\G;
从库这2个都为Yes才代表成功,有一个是No都不成功!!!
lave_IO_Running: Yes
Slave_SQL_Running: Yes
回到从主数据库B192.168.1.111
A《——B,B是A的从库配置:
MASTER_HOST=主机IP(主从数据库IP地址)
MASTER_USER=主机用户
MASTER_PASSWORD=主机用户密码
MASTER_LOG_FILE=对应上面的File的值
MASTER_LOG_POS=对应上面的Position的值
建议创建一个单独用来主从复制的账号,如:copy
CHANGE MASTER TO MASTER_HOST='192.168.1.11',MASTER_USER='copy',MASTER_PASSWORD='copy',MASTER_LOG_FILE='mariadb-bin.000001',MASTER_LOG_POS=330;
启动主从复制
start slave;
正确结果如下:
[anolis@localhost ~]$ mysql -u root -p
Enter password:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 3
Server version: 10.6.10-MariaDB MariaDB Server
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
#建议创建一个单独用来主从复制的账号,如:copy
MariaDB [(none)]> CHANGE MASTER TO MASTER_HOST='192.168.1.11',MASTER_USER='root',MASTER_PASSWORD='123456',MASTER_LOG_FILE='mariadb-bin.000001',MASTER_LOG_POS=330;
Query OK, 0 rows affected (0.008 sec)
MariaDB [(none)]> start slave;
Query OK, 0 rows affected (0.001 sec)
主从库查看主从复制是否成功
SHOW SLAVE STATUS\G;
从库这2个都为Yes才代表成功,有一个是No都不成功!!!
lave_IO_Running: Yes
Slave_SQL_Running: Yes
Slave failed to initialize relay log info structure from the repository错误
解决方法:
停止全部的主从复制,即A和B都执行以下操作
STOP SLAVE;
执行reset slave删除ralaylog日志文件,并重新启用新的relaylog文件
reset slave all;
A192.168.1.11
CHANGE MASTER TO MASTER_HOST='192.168.1.111',MASTER_USER='copy',MASTER_PASSWORD='copy',MASTER_LOG_FILE='mariadb-bin.000002',MASTER_LOG_POS=333;
B192.168.1.111
CHANGE MASTER TO MASTER_HOST='192.168.1.11',MASTER_USER='copy',MASTER_PASSWORD='copy',MASTER_LOG_FILE='mariadb-bin.000001',MASTER_LOG_POS=330;
A和B都执行启动主从复制
start slave;
A和B都执行查看主从复制是否成功
SHOW SLAVE STATUS\G;
lave_IO_Running: Yes
Slave_SQL_Running: Yes
都为Yes才代表成功,有一个是No都不成功!!!