mariadb配置主从及备份
centos7 mariadb安装使用
Red Hat Enterprise Linux/CentOS 7.0 发行版已将默认的数据库从 MySQL 切换到 MariaDB。
一、添加 MariaDB yum 仓库
1、首先在 RHEL/CentOS 和 Fedora 操作系统中添加 MariaDB 的 YUM 配置文件 MariaDB.repo 文件
#编辑创建mariadb.repo仓库文件
vi /etc/yum.repos.d/mariadb.repo
2、添加repo仓库配置
[mariadb]
name = MariaDB
baseurl = http://mirrors.aliyun.com/mariadb/yum/10.11/centos7-amd64/
gpgkey = https://mirrors.aliyun.com/mariadb/yum/RPM-GPG-KEY-MariaDB
gpgcheck = 1
enabled = 1
二、在 CentOS 7 中安装 MariaDB
1、当 MariaDB 仓库地址添加好后，你可以通过下面的一行命令轻松安装 MariaDB
yum install mariadb-server mariadb-client -y
2、启动mariadb相关命令
systemctl start mariadb #启动MariaDB
systemctl stop mariadb #停止MariaDB
systemctl restart mariadb #重启MariaDB
systemctl enable mariadb #设置开机启动
三、初始化mysql
1、执行命令初始化
mariadb-secure-installation
在确认 MariaDB 数据库软件程序安装完毕并成功启动后请不要立即使用。为了确保数据 库的安全性和正常运转，需要先对数据库程序进行初始化操作。这个初始化操作涉及下面 5 个 步骤。
➢ 设置 root 管理员在数据库中的密码值(注意，该密码并非 root 管理员在系统中的密 码，这里的密码值默认应该为空，可直接按回车键)。
➢ 设置 root 管理员在数据库中的专有密码。y
➢ 随后删除匿名账户，并使用 root 管理员从远程登录数据库，以确保数据库上运行的业务的安全性。y
➢ 删除默认的测试数据库，取消测试数据库的一系列访问权限。y
➢ 刷新授权列表，让初始化的设定立即生效。y
2、mysql基本命令
中文编码设置，编辑mysql配置文件/etc/my.cnf，下入以下内容
[mysqld]
character-set-server=utf8
collation-server=utf8_general_ci
log-error=/var/log/mysqld.log
max_allowed_packet=1024M
[client]
default-character-set=utf8
[mysql]
default-character-set=utf8
生产环境里不会死磕root用户，为了数据库的安全以及和其他用户协同管理数据库，就需要创建其他数据库账户，然后分配权限，满足工作需求
MariaDB [(none)]> create user yuchao@'127.0.0.1' identified by 'redhat123';
MariaDB [(none)]> create user yuchao@localhost identified by 'redhat123';
通过localhost连接到mysql是使用UNIX socket，而通过127.0.0.1连接到mysql是使用TCP/IP
数据库权限设置
MariaDB [(none)]> grant all privileges on *.* to yuchao@127.0.0.1;
grant 权限 on 数据库.表名 to 账户@主机名 对特定数据库中的特定表授权
grant 权限 on 数据库.* to 账户@主机名 对特定数据库中的所有表给与授权
grant 权限1,权限2,权限3 on *.* to 账户@主机名 对所有库中的所有表给与多个授权
grant all privileges on *.* to 账户@主机名 对所有库和所有表授权所有权限
远程连接设置哦设置所有库，所有表的所有权限，赋值权限给所有ip地址的root用户
mysql > grant all privileges on *.* to root@'%' identified by 'password';
#创建用户
mysql > create user 'username'@'%' identified by 'password';
#刷新权限
flush privileges;
移除权限
MariaDB [(none)]> revoke all privileges on *.* from yuchao@127.0.0.1;
四.数据库备份与恢复
1、命令格式
mysqldump [选项] 数据库名 [表名] > 脚本名
mysqldump [选项] --数据库名 [选项 表名] > 脚本名
mysqldump [选项] --all-databases [选项] > 脚本名
mysqldump -u root -p -A -F > /opt/all1-date +%F.sql #参数：-A, --all-databases Dump all the databases -F, --flush-logs 在开始导出前，洗掉在MySQL服务器中的日志文件。
远程数据库备份：mysqldump -h主机名 -P端口 -u用户名 -p密码 --database 数据库名 > 文件名.sql
远程数据库压缩备份：mysqldump -h主机名 -P端口 -u用户名 -p密码 --database 数据库名 | gzip > 文件名.sql.gz
2、实例
备份所有数据库：
mysqldump -uroot -p --all-databases > /backup/mysqldump/all.sql
备份指定数据库：
mysqldump -uroot -p test > /backup/mysqldump/test.sql
备份指定数据库指定表(多个表以空格间隔)
mysqldump -uroot -p mysql db event > /backup/mysqldump/2table.sql
备份指定数据库排除某些表
mysqldump -uroot -p test --ignore-table=test.t1 --ignore-table=test.t2 > /backup/mysqldump/test2.sql
3、还原
第一种方法：系统行命令
mysqladmin -uroot -p create db_name
mysql -uroot -p db_name < /backup/mysqldump/db_name.sql
注：在导入备份数据库前，db_name如果没有，是需要创建的； 而且与db_name.db中数据库名是一样的才可以导入。
第二种方法：source方法
mysql > use db_name;
mysql > source /backup/mysqldump/db_name.sql
五、主从复制 参考文档：https://cloud.tencent.com/developer/article/1381642
mariadb主从复制中：
第一步：master记录二进制日志。在每个事务更新数据完成之前，master在二日志记录这些改变。Mariadb将事务写入二进制日志，即使事务中的语句都是交叉执行的。在事件写入二进制日志完成后，master通知存储引擎提交事务。
第二步：slave将master的binary log拷贝到它自己的中继日志。首先，slave开始一个工作线程——I/O线程。I/O线程在master上打开一个普通的连接，然后开始binlog dump process。Binlog dump process从master的二进制日志中读取事件，如果已经执行完master产生的所有文件，它会睡眠并等待master产生新的事件。I/O线程将这些事件写入中继日志。
第三步：SQL slave thread（SQL线程）处理该过程的最后一步。SQL线程从中继日志读取事件，并重新执行其中的事件而更新slave的数据，使其与master中的数据一致。
1、主机配置
主库：CenterOS7.5-64位 1核1G 系统盘：50GB 高性能云硬盘 内网IP:10.249.16.89
从库：CenterOS7.5-64位 1核1G 系统盘：50GB 高性能云硬盘 内网IP:10.249.16.64
2、同上1-4安装部署mariadb
3、登入主库并创建数据库和表
[root@主库 ~]# mysql -h 127.0.0.1 -u root -p #连接数据库
mariadb> create database db1;
mariadb> use db1;
mariadb> create table test (name VARCHAR(20), sex CHAR(1), birth DATE, birthaddr VARCHAR(20));
mariadb> insert into test values ('abc','f','1995-10-22','china');
desc test1; 打印表结构
4、停止mariadb主服务
[root@主库 ~]# systemctl stop mariadb
5、配置mariadb主要同步的数据库名字并开启对应的二进制日志
[mysqld]
character-set-server=utf8
collation-server=utf8_general_ci
log-error=/var/log/mysqld.log
max_allowed_packet=1024M
log-bin=mysql-bin #启用二进制日志，默认存在/var/lib/mariadb 下面
server-id=1 #本机数据库ID 唯一标示
binlog-do-db=db1 #可以被从服务器复制的库。二进制需要同步的数据库名，binlog_do_db：此参数表示只记录指定数据库的二进制日志，默认全部记录。binlog_ignore_db：此参数表示不记录指定的数据库的二进制日志。
[client]
default-character-set=utf8
[mysql]
default-character-set=utf8
6、重新启动
[root@主库 ~]# systemctl start mariadb
7、在主库中复制的授权
mariadb> grant replication slave on *.* to yh@10.249.16.64 identified by "123";
8、在从从库上测试登录
[root@从库 ~]# mysql -h 10.249.16.89 -u yh -p
9、复制前保证主从两个数据库数据一致，把主的原始数据传给从，导出所有数据库：
[root@主库 ~]#mysqldump -u root -p -A -F > /opt/all1-`date +%F`.sql #参数：-A, --all-databases Dump all the databases -F, --flush-logs 在开始导出前，洗掉在MySQL服务器中的日志文件。
如果远程拷贝要加入-h 参数，在实际生产中需要增加字符集
10、数据库复制到从库上
[root@从库 ~]#scp root@10.129.16.89:/opt/all1.sql /opt/
11、在mariadb从服务器从库上导入
[root@从库 ~]# mysql -u root -p < /opt/all1.sql #导入数据库，和主服务器保持一致
Enter password: #YH/2019s2b，直接回车
mariadb> show databases;
mariadb> use db1;
mariadb> show tables;
12、停服务修改/etc/my.cnf
systemctl stop mariadb
[mysqld]
character-set-server=utf8
collation-server=utf8_general_ci
log-error=/var/log/mysqld.log
max_allowed_packet=1024M
server-id=2 #主要这个配置，id不能和主数据库相同
read-only=1 #设置从服务器为只读模式
[client]
default-character-set=utf8
[mysql]
default-character-set=utf8
13、启动服务
systemctl start mariadb
14、在主库89上查看
mariadb> show master status;
ERROR 2006 (HY000): Mariadb server has gone away
No connection. Trying to reconnect...
Connection id: 2
Current database: db1
+-----------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+-----------------+----------+--------------+------------------+
| mariadblog.000001 | 245 | db1 | |
15、在从库64执行
MariaDB [(none)]> change master to master_host='10.249.16.89', master_user='yh', master_password='123', master_port=3306, master_log_file='mariadblog.000001', master_log_pos=396, master_connect_retry=10;
MariaDB [(none)]> start slave; #启动从服务
MariaDB [(none)]> show slave status \G #查看从服务器状态
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 172.17.32.15
Master_User: slave
Master_Port: 3306
Connect_Retry: 10
Master_Log_File: mariadblog.000001
Read_Master_Log_Pos: 396
Relay_Log_File: mariadb-relay-bin.000002
Relay_Log_Pos: 530
Relay_Master_Log_File: mariadblog.000001
Slave_IO_Running: Yes #主要关注此参数一个负责与主机的io通信
Slave_SQL_Running: Yes #主要关注此参数负责自己的slave mariadb进程
16、测试数据同步
主库写数据89
mariadb> use db1;
Database changed
mariadb> show tables;
+--------------+
| Tables_in_db1 |
+--------------+
| test1 |
+--------------+
1 row in set (0.00 sec)
mariadb> insert into test values('def','a','1996-09-01','china');
从库读数据64
mariadb> use db1;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mariadb> select * from test1;
17、查看主库的binlog日志
> show binlog events;
18、排错：
同步之前如果怀疑主从数据不同步可以采取：上面冷备份远程拷贝法或者在从服务器上命行同步方法。
总结：
1、 主从同步的原理
2、 主从同步的配置
3、 测试主从同步是否成功
六、定时备份MariaDB/MySQL
1.创建备份文件位置
cd /home
mkdir backup_db
cd backup_db
pwd
2、创建脚本
vim backup-mariadb.sh
#!/bin/bash
backupdir='/home/backup_db/'
time=`date +%Y%m%d`
mysqldump -uroot -p密码 --all-databases > $backupdir/$time.sql
3、设置运行权限
chmod +x backup-mariadb.sh
4、安装crond服务
yum install crontabs
5、查看当前定时任务列表
crontab -l
6、编辑定时任务
crontab -e
* * * * * /home/backup_db/backup-mariadb.sh # 分 时 日 月 年
7、保存后重启
service crond restart
8、查看备份路径已有备份文件
9、把启动定时任务的开关放进 /etc/rc.d/rc.local 中，开启自启动服务
service crond start
七、naticat远程访问权限
> use mysql;
> update user set host = '%' where user = 'root';
> flush privileges;
> select host,user from user;
#此步骤设置了以root用户访问的任何IP都以'root'作为密码
> GRANT ALL PRIVILEGES ON *.* TO'root'@'%' IDENTIFIED BY 'root' WITH GRANT OPTION;
> flush privileges;
