安装MariaDB
《安装MariaDB教程》
主从复制
搭建主从复制数据库不能先建数据库,有数据库,需要先删除!!!
Master主数据库
《主从复制详细参数列表》
主服务器MariaDB的/etc/my.cnf配置(没有/etc/my.cnf就创建,改/etc/my.cnf.d/server.cnf是没用的!!!如果指定了datadir和basedir需要同时在my.cnf和server.cnf配置)
/etc/my.cnf
[mysqld]
datadir=/data/mariaDB_data
character_set_server=utf8
ssl
ssl-ca=ca.pem
ssl-cert=server-cert.pem
ssl-key=server-key.pem
server-id=200
log-bin=mariadb-bin
binlog-ignore-db=mysql,information_schema,performance_schema,sys
binlog_format=MIXED
expire_logs_days=30
relay-log=mariadb-relay
log-slave-updates
plugin-load=simple_password_check.so
disconnect_on_expired_password=on
simple_password_check_digits=1
simple_password_check_letters_same_case=1
simple_password_check_minimal_length=8
simple_password_check_other_characters=1
default_password_lifetime=365
max_password_errors=5
interactive_timeout=7200
wait_timeout=7200
[mariadb]
log_output=TABLE
general_log
/etc/my.cnf.d/server.cnf
[mysqld]
datadir=/data/mariaDB_data
character_set_server=utf8
重启主MariaDB,主要是使my.cnf配置文件生效
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代表成功!
查看Master主数据库的状态
show master status;
+--------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+--------------------+----------+--------------+------------------+
| mariadb-bin.000001 | 330 | demo | |
+--------------------+----------+--------------+------------------+
file的值后面会用到,Binlog_Do_DB就是需要主从复制的数据库。
Slave从数据库
从服务器MariaDB的/etc/my.cnf配置(没有/etc/my.cnf就创建,改/etc/my.cnf.d/server.cnf是没用的!!!如果指定了datadir和basedir需要同时在my.cnf和server.cnf配置)
[必填]启用中继日志:relay-log=mariaDB-relay
[必须]MariaDB服务器唯一ID:server-id=2
/etc/my.cnf
[mysqld]
datadir=/data/mariaDB_data
character_set_server=utf8
ssl
ssl-ca=ca.pem
ssl-cert=server-cert.pem
ssl-key=server-key.pem
server-id=201
log-bin=mariadb-bin
binlog-ignore-db=mysql,information_schema,performance_schema,sys
binlog_format=MIXED
expire_logs_days=30
relay-log=mariadb-relay
log-slave-updates
plugin-load=simple_password_check.so
disconnect_on_expired_password=on
simple_password_check_digits=1
simple_password_check_letters_same_case=1
simple_password_check_minimal_length=6
simple_password_check_other_characters=1
default_password_lifetime=365
max_password_errors=5
interactive_timeout=7200
wait_timeout=7200
[mariadb]
log_output=TABLE
general_log
/etc/my.cnf.d/server.cnf
[mysqld]
datadir=/data/mariaDB_data
character_set_server=utf8
重启从MariaDB,主要是使my.cnf配置文件生效
systemctl restart mariadb
登录Slave从数据库进行主从复制配置
Slave从机配置:
MASTER_HOST=主机IP
MASTER_PORT=3306(注意:端口是数字类型,不要加单引号或者双引号)
MASTER_USER=主机用户
MASTER_PASSWORD=主机用户密码
MASTER_LOG_FILE=对应上面的File的值
MASTER_LOG_POS=对应上面的Position的值(注意: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.
MariaDB [(none)]> 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;
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
MariaDB [(none)]> SHOW SLAVE STATUS\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.56.58
Master_User: root
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mariadb-bin.000002
Read_Master_Log_Pos: 330
Relay_Log_File: mariaDB-relay.000002
Relay_Log_Pos: 557
Relay_Master_Log_File: mariadb-bin.000002
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Slave failed to initialize relay log info structure from the repository错误
解决方法:
从库先停止主从复制,重置主从复制。
停止主从复制
STOP SLAVE;
执行reset slave删除salve数据库的ralaylog日志文件,并重新启用新的relaylog文件
reset slave all;
然后再回到前面登录Slave从数据库进行主从复制配置这个标题重新操作一次!
本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系:hwhale#tublm.com(使用前将#替换为@)