1.部署
10.0.0.21 MariaDB-21
10.0.0.22 MariaDB-22
cat /etc/yum.repos.d/mariadb.repo [mariadb] name=MariaDB baseurl=http://mirrors.ustc.edu.cn/mariadb/yum/10.2/centos7-amd64/ gpgkey=http://mirrors.ustc.edu.cn/mariadb/yum/RPM-GPG-KEY-MariaDB gpgcheck=1 yum -y install MariaDB-server MariaDB-client systemctl start mariadb # 除了将密码改为mariadb123456,其余步骤都按Y /usr/bin/mysql_secure_installation # 如果需要修改默认的数据路径,先不要启动mariadb mkdir -p /data/mariadb chown -R mysql:mysql /data/mariadb # 将默认的数据拷贝到新的路径下面 cp -a /var/lib/mysql/* /data/mariadb/ # 修改配置文件/etc/my.cnf.d/server.cnf [mysqld] datadir=/data/mariadb socket=/var/lib/mysql/mysql.sock character_set_server=utf8 slow_query_log=on slow_query_log_file=/data/mariadb/logs/slow_query_log.log long_query_time=2 log-error =/data/mariadb/logs/error.log # 本实验一切按照默认的来
2.配置主从复制
a.在10.0.0.21上配置主服务器
innodb_file_per_table:拆分表数据存储 log-bin:开启二进制日志 cat /etc/my.cnf.d/server.cnf [mysqld] skip_name_resolve = ON innodb_file_per_table = ON server-id = 21 log-bin = master-bin log-bin-index=master-bin.index systemctl restart mariadb.service mysql -uroot -pmariadb123456 grant replication slave,replication client on *.* to 'repluser'@'10.0.0.%' identified by 'repluser123456'; flush privileges; # 查看主服务器的状态信息,在从服务器中要用到 show master statusG File: master-bin.000001 Position: 676
b.在10.0.0.22上配置从服务器
从服务器最好启用read_only=ON禁止写操作,防止误操作 cat /etc/my.cnf.d/server.cnf [mysqld] skip_name_resolve = ON innodb_file_per_table = ON server-id = 22 relay_log = slave_relay_bin systemctl restart mariadb.service mysql -uroot -pmariadb123456 change master to master_host='10.0.0.21',master_user='repluser', master_password='repluser123456',master_log_file='master-bin.000001',master_log_pos=676; start slave; show slave statusG Slave_IO_Running: Yes Slave_SQL_Running: Yes
测试,在主服务器上创建库,创建表,插入数据,可以在从服务器上看到数据被同步过来
create database TestDB;
use TestDB;
create table student(id int,name varchar(20));
insert into student(id,name) values('1','zhangsanfeng');
insert into student(id,name) values('2','budaiheshang'),('3','wusanren');
insert into student set id=4,name="zhangwuji";
3.配置主主复制
在上面主从复制的基础上继续做,即:在之前的master上开启relay_log,在之前的slave上开启log-bin,并且把步进值改为n
auto_increment_offset:起始值,一般填第n台主MySQL,此时为第一台主MySQL
auto_increment_increment:步进值,一般有n台主MySQL就填n
binlog-ignore:忽略某个库,这里先不用这个参数
replicate-do-db:要同步的数据库,默认所有库
10.0.0.21的配置文件内容如下
cat /etc/my.cnf.d/server.cnf [mysqld] skip_name_resolve = ON innodb_file_per_table = ON server-id = 21 auto_increment_offset = 1 auto_increment_increment = 2 relay_log = slave_relay_bin log-bin = master-bin log-bin-index = master-bin.index
10.0.0.22的配置文件内容如下
cat /etc/my.cnf.d/server.cnf [mysqld] skip_name_resolve = ON innodb_file_per_table = ON server-id = 22 relay_log = slave_relay_bin auto_increment_offset = 2 auto_increment_increment = 2 log-bin = master-bin log-bin-index = master-bin.index
修改完配置文件之后,两台服务器都要重启
systemctl restart mariadb.service # 在10.0.0.22上授权用户 mysql -uroot -pmariadb123456 grant replication slave,replication client on *.* to 'repluser'@'10.0.0.%' identified by 'repluser123456'; flush privileges; # 查看二进制日志名和位置 show master statusG File: master-bin.000001 Position: 329 # 在10.0.0.21中执行 change master to master_host='10.0.0.22',master_user='repluser', master_password='repluser123456',master_log_file='master-bin.000001',master_log_pos=329; start slave;
完成主主复制配置,在10.0.0.22上进行测试,可在21上看到数据已经被同步
use TestDB;
create table tab1(id int auto_increment,name varchar(10),primary key(id));
insert into tab1 (name) value('zhangsan'),('lisi'),('wangmazi');
主主复制配置文件中auto_increment_increment和auto_increment_offset只能保证主键不重复,却不能保证主键有序.
主从|主主写的不错:https://www.cnblogs.com/phpstudy2015-6/p/6485819.html
主从|主主|半同步都有写:https://www.jb51.net/article/97786.htm