Mysql主从备份

一、基本权限命令

# 修改密码
MariaDB [(none)]> set password = PASSWORD('123456');

# 创建普通用户
MariaDB [(none)]> create user xiaoming@'%' identified by '123';

# 远程登陆
mysql -uxiaoming -p -h 192.168.11.59


# 查询mysql的用户表
select user,host,password from mysql.user;

 

二、数据库权限设置

grant 权限 on 数据库.表名 to 账户@主机名            对特定数据库中的特定表授权
grant 权限 on 数据库.* to 账户@主机名            　　对特定数据库中的所有表给与授权
grant 权限1,权限2,权限3 on *.* to 账户@主机名   　　 对所有库中的所有表给与多个授权
grant all privileges on *.* to 账户@主机名   　　 对所有库和所有表授权所有权限

# 给普通用户查询权限
grant select on *.* to xiaoming@'%';

# 查询用户的权限
show grants for xiaoming@'%';

# 移除用户的所有权限
revoke all privileges on *.* from xiaoming@'%';

 

三、数据库的备份和恢复

# 导出mysql的所有数据库
[root@localhost ~]# mysqldump -u root -p --all-databases > /tmp/db.sql

# 导入数据库方式1（在mysql环境了）
MariaDB [mysql]> source /tmp/db.sql;

# 导入数据库方式2
[root@localhost ~]# mysql -u root -p < /tmp/db.sql

# 导入数据库方式3
使用navicat

# 导入数据库方式4，针对数据量特别大
使用xtrabackup

 

四、主从同步

主从同步实现读写分离

　　master     -->   可读可写数据

　　slave　　-->    可读数据

 

主从机制实现原理

(1) master将改变记录到二进制日志(binary log)中（这些记录叫做二进制日志事件，binary log events）； 
(2) slave将master的binary log events拷贝到它的中继日志(relay log)； 
(3) slave重做中继日志中的事件，将改变反映它自己的数据。


2.配置master数据库

# 1.关闭数据库
[root@localhost ~]# systemctl stop mariadb

# 2.修改配置文件
[root@localhost ~]# vim /etc/my.cnf

'''添加以下代码'''
#解释：server-id服务的唯一标识（主从之间都必须不同）；log-bin启动二进制日志名称为mysql-bin 
[mysqld]
server-id=1
log-bin=mysql-bin


#3.启动数据库
[root@localhost ~]# systemctl start mariadb

 

 2.master主库创建并授权同步的用户

# 1.创建用户
MariaDB [(none)]> create user haha@'%' identified by '123';

# 密码提示太简单
MariaDB [(none)]> set global validate_password_policy=0;

# 2.授权用户
MariaDB [(none)]> grant replication slave on *.* to haha@'%';

# 3.查询用户权限
MariaDB [(none)]> show grants for haha@'%';

# 4.实现对主数据库锁表只读，防止数据写入
MariaDB [(none)]> flush table with read lock;

# 5.查询主库状态
MariaDB [(none)]> show master status;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 |      605 |              |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

# 6.打开另一个终端，导出所有数据库
[root@localhost ~]# mysqldump -u root -p --all-databases > /data/sql.db

# 7.将数据库文件通过scp发送至slave
scp /data/sql.db ip@root:/data/

 

3.slave从库配置

# 1.修改配置文件
vim /etc/my.cnf
[mysqld]
server-id=3
read-only=true

# 2.重启数据库
systemctl restart mariadb

# 3.使用root用户登陆数据库

# 4.导入数据
mysql > source /data/db.sql

# 5.开启主从之间的复制关系
mysql > change master to master_host='192.168.11.59',
master_user='haha',
master_password='123',
master_log_file='mysql-bin.000001',
master_log_pos=605;

# 6.开启同步功能
mysql > start slave

# 7.查看是否同步
mysql > show slave status\G;

 

4.主库解锁

unlock tables;

 

5.注意事项

1.从库配置后退出root用户，使用普通用户登陆

2.注意普通用户的授权问题