Linux 搭建数据库 Mariadb 主主主从半同步复制

笨小孩@GF 知行合一

已于 2024-06-05 17:08:53 修改

阅读量10w+

点赞数

分类专栏：学习笔记文章标签： Linux Mariadb 数据库主主主从半同步数据库 mysql mariadb 主从同步

于 2020-01-05 16:21:24 首次发布

本文链接：https://blog.csdn.net/gaofei0428/article/details/103829676

版权

学习笔记专栏收录该内容

125 篇文章 69 订阅

订阅专栏

MariaDB

MariaDB数据库管理系统是MySQL的一个分支，主要由开源社区在维护，采用GPL授权许可 MariaDB的目的是完全兼容MySQL，包括API和命令行，使之能轻松成为MySQL的代替品。在存储引擎方面，使用XtraDB（英语：XtraDB）来代替MySQL的InnoDB。 MariaDB由MySQL的创始人Michael Widenius（英语：Michael Widenius）主导开发，他早前曾以10亿美元的价格，将自己创建的公司MySQL AB卖给了SUN，此后，随着SUN被甲骨文收购，MySQL的所有权也落入Oracle的手中。MariaDB名称来自Michael Widenius的女儿Maria的名字。

MariaDB基于事务的Maria存储引擎，替换了MySQL的MyISAM存储引擎，它使用了Percona的 XtraDB，InnoDB的变体，分支的开发者希望提供访问即将到来的MySQL 5.4 InnoDB性能。这个版本还包括了 PrimeBase XT (PBXT) 和 FederatedX存储引擎。

MariaDB直到5.5版本，均依照MySQL的版本。因此，使用MariaDB5.5的人会从MySQL 5.5中了解到MariaDB的所有功能。

从2012年11月12日起发布的10.0.0版开始，不再依照MySQL的版号。10.0.x版以5.5版为基础，加上移植自MySQL 5.6版的功能和自行开发的新功能。

MariaDB虽然被视为MySQL数据库的替代品，但它在扩展功能、存储引擎以及一些新的功能改进方面都强过MySQL。而且从MySQL迁移到MariaDB也是非常简单的：

数据和表定义文件（.frm）与二进制兼容
所有客户端API、协议和结构都完全一致
所有文件名、二进制、路径、端口等都一致
所有的MySQL连接器，比如PHP、Perl、Python、Java、.NET、MyODBC、Ruby以及MySQL C connector等在MariaDB中都保持不变
mysql-client包在MariaDB服务器中也能够正常运行
共享的客户端库与MySQL也与二进制兼容

也就是说，在大多数情况下，你完全可以卸载MySQL然后安装MariaDB，然后就可以像之前一样正常的运行。

安装 Mariadb

配置安全脚本

安装完成后，root密码默认为空，因此需要进行配置安全控制程序。

在配置前需要先启动mariadb

使用 mysql_secure_installation 启动安全配置程序，输入当前 root 密码，因为当前密码为空，直接回车

是否设置 root 密码，这里输入 y 表示设置密码，输入2次密码，显示 success 为成功

是否删除匿名用户，选择删除

是否禁止 root 用户远程登录数据库，设置为是

是否删除test数据库，这里为了后面测试选择不删除

是否重载授权表，选择是

配置完成，下面使用 mysql 命令登录数据库

输入 help 可以得到帮助

使用 show databases; 可以查看所有数据库

使用 use 数据库名称; 进入数据库

使用 show tables; 查看表列表

使用 desc 表名称; 查看表结构

使用 select version(); 查看版本信息

输入 quit 或者 exit 退出数据库，使用 mysqladmin -u root -p version 也可以查看版本信息

数据库的日常操作不外乎增、改、删、查，下面我们一一实践

创建新数据库
create database 数据库名称;
指定默认字符 utf8
create database DBNAME default charset 'utf8' collate 'utf8_bin';

删除数据库
drop database 数据库名称;

创建新用户
create user 'tom'@'localhost' identified by 'password'; 'password' 为用户的口令
（注：命令、参数不区分大小写，注意命令后面的分号 ; ）

授权用户访问数据库
GRANT ALL PRIVILEGES ON mydata.* to '用户名'@'localhost'; 授权某一用户使用 mydata 数据库
GRANT ALL PRIVILEGES ON *.* to '用户名'@'localhost'; *.*代表所有数据库
grant select,insert,delete,update on mydata.* to '用户名'@'localhost' identified by 'PASSWORD'; 为用户授予部分权限

命令成功完成后需要刷新权限

删除用户
Delete FROM mysql.user Where User='用户名' and Host='localhost';
drop user '用户名'@'localhost'; 或者 drop user '用户名'@'%';
drop user ''@'localhost';

更改用户密码
use mysql;
update user set password=PASSWORD("new_password") where user="用户名";
或者
alter user '用户名'@'IP' IDENTIFIED BY 'PASSWORD';
flush privileges; 修改完密码必须更新权限

创建表格
use mydata; 选择在哪个数据库内创建
create table mylist (
-> id int not null,
-> name varchar(30),
-> primary key (id));　　该字段列值唯一，"primary key" 表示该列是表的主键, MySQL将自动索引该列

查看表格创建过程

在 mylist 表中插入一条信息

更改表名称

删除表内数据

更改表里的内容

增加表里的条目

删除表里的条目

更改表里的列名

更改列数据类型

删除表格
DROP TABLE table_name (删除表);

备份数据库
mysqldump -h主机名 -P端口 -u用户名 -p密码 --database 数据库名 > 文件名.sql
mysqldump -u root -p --all-databases > /Your_backup_PWD/all_dbs.sql 备份所有数据库

同时备份多个库
mysqldump -h主机名 -P端口 -u用户名 -p密码 --databases 数据库名1 数据库名2 数据库名3 > 文件名.sql
mysqldump -h192.168.10.2 -uroot -ppassword --databases mydata mysql > /Your_backup_PWD/db_mydata_mysql.sql

备份同个库多个表
mysqldump -h主机名 -u用户名 -p密码 --database 数据库名表1 表2 .... > 文件名.sql
mysqldump -h192.168.10.2 -uroot -ppassword mydata mylist mylist1 > /Your_backup_PWD/mydata_mylist_mylist1.sql

备份数据库结构，不备份数据
mysqldump --opt -d 数据库名 -u用户名 -p密码 > 文件名.sql
-d只导出结构 -t只导出数据
mysqldump --opt -d mydata > /Your_backup_PWD/structure.sql

恢复数据库
作为测试，在恢复之前先将原先的数据库删除
mysql -uroot -p123456 -e "drop database mydata;"
mysql -uroot -p123456 -e "show databases;"

恢复删除的数据库
mysql -uroot -p123456 < /Your_backup_PWD/备份文件.sql

压缩备份
mysqldump -uroot -p123456 -B mydata | gzip > /Your_backup_PWD/备份文件.sql.gz

恢复压缩备份
gunzip < /Your_backup_PWD/备份文件.sql.gz | mysql -uroot -p123456

开启mysqlbinlog 实现增量备份
编辑 /etc/my.cof 配置文件，增加一行 log-bin=/data/mysql-bin 指明bin-log存放路径，注意/data目录的权限为 mysql.mysql
二进制日志文件与数据库数据文件最好不要放在同一块硬盘上，如果存放数据文件的硬盘坏了，可以用另一块硬盘的二进制日志来恢复数据

重新启动mariadb 查看 /data 目录是否有bin-log文件

flush logs; 会多一个最新的bin-log日志
show master status; 查看最后一个bin-log日志的相关信息
reset master; 清空所有的bin-log日志

使用 mysqlbinlog 命令可以查看 bin-log 文件

使用二进制日志还原数据库
mysqlbinlog /Your_Bin-log_DIR/mysql-bin.000001 | mysql -u root -p
这条命令可以理解为:使用mysqlbinlog读取二进制日志文件然后使用mysql命令还原到数据库中
注意还原时必须是编号小的先还原

删除二进制日志
reset master；删除所有
purge master logs to ‘mysql-bin.000004‘; 删除编号000004以前的所有
purge master logs to ‘2019-10-1 12:00:00‘; 删除指定日期时间之前的所有

查看mysql服务器是否开启binlog日志，查看binlog文件存放位置与名称

开启binlog之后，备份数据 -A是所有库 -l 是锁库的意思，-F 是备份成功后刷新binlog日志的意思，即生成一份新binlog日志文件
mysqldump -uroot -p123456 -A -l -F > /Your_backup_PWD/备份文件名称.sql

主从复制
主数据库所在服务器IP:192.168.10.2
从数据库所在服务器IP:192.168.10.3
mariadb的配置文件的位置：/etc/my.cnf

首先在主数据库端编辑 /etc/my.cnf

[mysqld]
log-bin=/data/mysql-bin # binlog二进制日志存放位置
server-id=1 #主机标识ID号，不允许重复
binlog-ignore-db=mysql # 指明 igonre-db 忽略同步的库
binlog-ignore-db=information_schema
binlog-ignore-db=performance_schema
binlog-ignore-db=test
innodb_flush_log_at_trx_commit=1
binlog-do-db=database_name #指明 do-db 需要同步的库
replicate-do-db=database_name #指明同步那些库

lower_case_table_names=1 #关闭大小写敏感

[mysqld]
log-bin=/data/mysql-bin
server-id=1 
binlog-ignore-db=mysql
binlog-ignore-db=information_schema
binlog-ignore-db=performance_schema
binlog-ignore-db=test
innodb_flush_log_at_trx_commit=1
binlog-do-db=database_name
replicate-do-db=database_name
lower_case_table_names=1

mysql8.0 需要初始化时设置
mysqld --user=mysql --lower-case-table-names=1 --initialize-insecure --datadir=/data/mysql
如果 /data/mysql 目录已经存在，需要手动删除
添加一个用户 (slave) 并授权，重启 Mariadb 服务，查看记录log-bin文件名和pos号
create user 'mycat'@'192.168.2.%' identified by 'MyCat@123456';

grant all on *.* to 'mycat'@'192.168.2.%';
select user,host,plugin,authentication_string from mysql.user;
grant replication slave on *.* to '授权用户名'@'%' identified by '授权用户密码';
GRANT ALL PRIVILEGES ON *.* TO 'mycat'@'%' IDENTIFIED BY '123456';
flush privileges; 刷新权限

然后在从服务器配置 /etc/my.cnf

log-bin=/data/mysql/mysql-bin 指明 bin-log 二进制文件存放位置
server-id=2 指明主机标识ID号，不允许重复
relay-log-index=/data/mysql/slave-relay-bin.index 定义relay_log的位置和名称

relay-log=/data/mysql/slave-relay-bin

log-bin=/data/mysql/mysql-bin
server-id=2
relay-log-index=/data/mysql/slave-relay-bin.index
relay-log=/data/mysql/slave-relay-bin

重启从数据库服务后登陆进行以下操作

stop slave; 停止slave复制
grant replication slave on *.* to '授权用户名'@'%' identified by '授权用户密码';
change master to master_host='主数据库IP地址',master_user='授权用户名',master_password='授权用户密码',master_log_file='主数据库BINLOG名称',master_log_pos=主数据库POS值;
start slave; 开启slave复制
show slave status\G 查看同步状况

出现一个报错
ERROR 1201 (HY000): Could not initialize master info structure; more error messages can be found in the MariaDB error log
查看 mariadb error log
tail /var/log/mariadb/mariadb.log
[ERROR] Failed to open the relay log '/data/slave-log.000036' (relay_log_pos 4)
reset slave all; 清空 slave log 信息

导入主数据库备份文件

在主数据库端添加一些数据，观测从数据库是否同步

主主复制
首先在主数据库端编辑 /etc/my.cnf 添加以下内容
log-bin=/data/master-log
server-id=1
relay-log=/data/slave-log
auto_increment_offset=1 定义自增长字段开始数值
auto_increment_increment=2 定义自增长字段每次递增的量值
binlog_format=mixed #混合模式复制(mixed-based replication, MBR)
log-slave-updates=1 #控制是否把所有的操作写入到binary log，默认是关闭的
max_binlog_size         = 100m              #binlog每个日志文件大小
binlog_cache_size       = 4m                 #binlog缓存大小
max_binlog_cache_size   = 512m        #最大binlog缓存大小

保存重启 mairadb 服务，登录后进行以下操作

stop slave; 停止slave复制
grant replication slave on *.* to '授权用户名'@'%' identified by '授权用户密码';
change master to master_host='主数据库IP地址',master_user='授权用户名',master_password='授权用户密码',master_log_file='主数据库BINLOG名称',master_log_pos=主数据库POS值;
start slave; 开启slave复制
show slave status\G 查看同步状况

然后在 192.168.10.5 从服务器端做以下操作
vim /etc/my.cnf 添加以下内容
log-bin=/data/master-log
server-id=2
relay-log=/data/slave-log
auto_increment_offset=2
auto_increment_increment=2
binlog_format=mixed #混合模式复制(mixed-based replication, MBR)
log-slave-updates=1 #控制是否把所有的操作写入到binary log，默认是关闭的
max_binlog_size         = 100m              #binlog每个日志文件大小
binlog_cache_size       = 4m                 #binlog缓存大小
max_binlog_cache_size   = 512m        #最大binlog缓存大小

保存重启 mairadb 服务，登录后进行以下操作

stop slave; 停止slave复制
grant replication slave on *.* to '授权用户名'@'%' identified by '授权用户密码';
change master to master_host='主数据库IP地址',master_user='授权用户名',master_password='授权用户密码',master_log_file='主数据库BINLOG名称',master_log_pos=主数据库POS值;
start slave; 开启slave复制
show slave status\G 查看同步状况

在2个服务器端分别添加、删除信息，验证同步是否正常
在 10.2 上添加一条信息

在 10.5 端查看

在 10.5 端删除一些信息

在 10.2 端查看

半同步复制
首先在主服务器端编辑 /etc/my.cnf 配置文件，添加以下内容
plugin-load=rpl_semi_sync_master=semisync_master.so 启用半同步插件
rpl_semi_sync_master_enabled=1 启用半同步复制
注：在某些高可用架构下，master和slave需同时启动，以便在切换后能继续使用半同步复制
即在主从数据库的my.cnf配置文件中都要添加:
plugin-load="rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so"

rpl-semi-sync-master-enabled=1

rpl-semi-sync-slave-enabled=1

在从服务器端编辑 /etc/my.cnf 配置文件，添加以下内容
plugin-load=rpl_semi_sync_slave=semisync_slave.so
rpl_semi_sync_slave_enabled=1

重启从数据库上的IO线程
STOP SLAVE IO_THREAD;
START SLAVE IO_THREAD;
注: 如果没有重启IO线程，则默认的还是异步复制模式

在主服务器端查看插件是否启用

登录后查看半同步状态
show status like 'Rpl_semi_sync_master_status';

从数据库端查看
show status like 'Rpl_semi_sync_slave_status';

接着测试一下是否为半同步复制
首先停止从数据库slave复制

在主数据库插入一些信息
发现第一条信息用了10.03sec，第二条Eric0.01sec，其实Mysql半同步复制并不是严格意义上的半同步复制。当半同步复制发生超时时（由rpl_semi_sync_master_timeout参数控制，单位是毫秒，默认为10000，即10s），会暂时关闭半同步复制，转而使用异步复制。当master dump线程发送完一个事务的所有事件之后，如果在rpl_semi_sync_master_timeout内，收到了从库的响应，则主从又重新恢复为半同步复制。[一旦有一次超时自动降级为异步].

在从数据库打开 slave 复制

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
log-bin=/data/mariadb/master-log
server-id=1
relay-log=/data/mariadb/slave-log
auto_increment_offset=1
auto_increment_increment=2
binlog_format=mixed
log-slave-updates=1
max_binlog_size=100m
binlog_cache_size=4m
max_binlog_cache_size=512m
binlog-ignore-db=mysql
innodb_flush_log_at_trx_commit=1
plugin-load="rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so"
rpl-semi-sync-master-enabled=1
rpl-semi-sync-slave-enabled=1
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
log-bin=/data/mariadb/master-log
server-id=2
binlog_format=mixed
log-slave-updates=1
relay-log=/data/mariadb/slave-log
auto_increment_offset=2
auto_increment_increment=2
max_binlog_size=100m
binlog_cache_size=4m
max_binlog_cache_size=512m
binlog-ignore-db=mysql
innodb_flush_log_at_trx_commit=1
plugin-load="rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so"
rpl-semi-sync-master-enabled=1
rpl-semi-sync-slave-enabled=1
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
log-bin=/data/mariadb/master-log
server-id=3
binlog_format=mixed
log-slave-updates=1
relay-log=/data/mariadb/slave-log
auto_increment_offset=3
auto_increment_increment=2
max_binlog_size=100m
binlog_cache_size=4m
max_binlog_cache_size=512m
binlog-ignore-db=mysql
innodb_flush_log_at_trx_commit=1
plugin-load="rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so"
rpl-semi-sync-master-enabled=1
rpl-semi-sync-slave-enabled=1

============================================================================================================

# rm -rf /etc/my.cnf
# rm -rf /var/lib/mysql/*
# chown -R mysql. /var/lib/mysql

忘记 mysql root 密码

1、编辑文件：vim /etc/my.cnf

2、在 [mysqld] 字段下面，按i键编辑；添加skip-grant-tables；按esc键，输入:wq保存退出

3、重启mysql:sudo service mysqld restart

第二步免密码登录mysql

1、登录：mysql -u root -p

2、提示输入密码按回车进入

3、进入数据库，输入：flush privileges;

use mysql；

4、查看root用户信息：select host,user,authentication_string,plugin from user;

5、更新root用户信息，把密码设置为空字符串：update user set authentication_string='' where user='root';
如果你的版本在10.4.4以上,使用如下命令重置密码

ALTER USER 'root'@'localhost' IDENTIFIED BY 'newpassword';

在10.4.4之前,使用如下命令重置密码

SET PASSWORD FOR 'root'@'localhost' = PASSWORD('newpassword');

第三步、退出mysql；注释掉/etc/my.cnf文件最后的 skip-grant-tables ；重启：sudo service mysqld restart 或者 systemctl restart mysqld

第四步：设置密码

1、重新开启一个客户端；

2、登录mysql(这时候还是不用输入密码，因为上面已经把密码设置为空字符串了);

3、修改root用户密码:ALTER user 'root'@'%' IDENTIFIED BY 'root123##ROOT';

flush privileges;

4、mysql8.0以上密码策略限制必须要大小写加数字特殊符号。退出mysql后就可以用密码登录了

update user set host = "%" where user = "root";

select user,host,authentication_string from user;
alter user 'root'@'%' IDENTIFIED BY 'MIMA';

[root@mysql1 ~]# vim /etc/my.cnf
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
binlog-ignore-db=mysql
log-bin=mysql-bin-master

character-set-client-handshake = FALSE
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci
init_connect='SET NAMES utf8mb4'

server-id=12
relay-log=slave-log
binlog_format= ROW
auto_increment_offset=1
auto_increment_increment=2
log-slave-updates=1
innodb_flush_log_at_trx_commit=2

max_binlog_size = 100m
max_binlog_cache_size = 512m
max_connections = 3000
max_connect_errors = 3000
table_open_cache = 2048
max_allowed_packet = 16M
binlog_cache_size = 16M
max_heap_table_size = 16M
tmp_table_size = 256M
read_buffer_size = 1024M
read_rnd_buffer_size = 1024M
sort_buffer_size = 1024M
join_buffer_size = 1024M
key_buffer_size = 8M
thread_cache_size = 8
query_cache_type = on
query_cache_size = 512M
query_cache_limit = 1024M
ft_min_word_len = 4
long_query_time = 1
symbolic-links=0
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
validate-password=OFF
lower_case_table_names=1
[mysql]
default-character-set = utf8mb4
[client]
default-character-set = utf8mb4

[root@mysql2 ~]# vim /etc/my.cnf
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
binlog-ignore-db=mysql
log-bin=mysql-bin-master

character-set-client-handshake = FALSE
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci
init_connect='SET NAMES utf8mb4'

server-id=13
relay-log=slave-log
binlog_format= ROW
auto_increment_offset=2
auto_increment_increment=2
log-slave-updates=1
innodb_flush_log_at_trx_commit=2