Mysql主从复制
1.mysql基本命令
mysql基本初始配置
# 1.启动mysql
systemctl start/stop /restart/status/ mariadb
# 2.linux客户端连接自己
mysql -u root -p -h 127.0.0.1
# 3.远程链接mysql服务端
mysql -u root -p -h 192.168.11.37
# 4.修改mysql密码
MariaDB[(none)]> set password = PASSWORD('新密码');
# 5.创建mysql用户 "%" 表示所有ip地址
MariaDB[(none)]> create user 用户名@"%" identified by '密码';
# 6.查询mysql库中的用户信息
MariaDB[(none)]> use mysql;
MariaDB[(none)]> select host,user,password from user;
授权配置
# 7. 授权语句
mysql使用grant命令对账户进行授权,grant命令常见格式如下
grant 权限 on 数据库.表名 to 账户@主机名 # 对特定数据库中的特定表授权
grant 权限 on 数据库.* to 账户@主机名 # 对特定数据库中的所有表给与授权
grant 权限1,权限2,权限3 on *.* to 账户@主机名 # 对所有库中的所有表给与多个授权
grant all privileges on *.* to 账户@主机名 # 对所有库和所有表授权所有权限
# 授予小春创建的权限,对于所有的库表生效
grant create on *.* to "xiaochun"@"%" identified by '密码';
# 授予小春用户mysql数据库的所有表能够查看的权限
grant select on mysql.* to "xiaochun"@"%" identified by "密码";
#授予小春用户,只有创建mymysql数据库的权限
grant create on mymysql.* to "xiaochun"@"%" identified by "密码";
#授予用户最大的权限,所有的权限
grant all privileges on *.* to "username"@"%";
#授予用户mymysql最大的权限,所有的权限
grant all privileges on mymysql.* to "xiaochun"@"%";
# 授权配置
# 远程连接设置哦设置所有库,所有表的所有权限,赋值权限给所有ip地址的root用户
mysql > grant all privileges on *.* to root@'%';
# 创建用户
mysql > create user 'username'@'%' identified by 'password';
# 刷新权限
flush privileges;
移除权限和备份导入
# 移除权限
MariaDB [(none)]> revoke all privileges on *.* from xiaochun@"%";
# 如果赋予单个权限就移除单个权限
MariaDB [(none)]> revoke all privileges on 数据表名.* from xiaochun@"%";
# 数据库的备份与恢复
# 备份
mysqldump -u root -p --all-databases > /tmp/db.sql
#数据导入,方式有2种
use db;
source /tmp/db.sql;
# 第二种
mysql -uroot -p db < /tmp/db.sql
# 第三种
navicat
# 第四种,如果你数据量特别大的话,使用第三方工具
xtrabackup
2.mysql主从复制
原理(*****)
主从复制七步曲(必会)
1. 主数据库写入数据之后, 会有data changes(数据变化)记录
2. 有变化记录之后,将增删改的一些sql语句记录到本地的Binary log(二进制日志)中
3. 从库会一直开启着一个线程
4. 通过线程去读取这个二进制日志的内容
5. 从库会将数据写入到自己的Relay log(中继日志)中
6. 从库会将中继日志中的操作转化为SQL thread(SQL语句)
7. 通过转化的SQL语句写入到自己的数据库, 两边的数据就一致了
(1) master将改变记录到二进制日志(binary log)中(这些记录叫做二进制日志事件,binary log events);
(2) slave将master的binary log events拷贝到它的中继日志(relay log);
(3) slave重做中继日志中的事件,将改变反映它自己的数据。
实现过程:
(1) 背景(两台数据库)
主数据库master: 10.0.3.82
从数据库slave: 10.0.3.85
(2) 在主数据库上进行操作
# 查看数据库状态
systemctl status mariadb
# 停mariadb # 也可以不关闭,直接修改,修改后重启用 systemctl restart mariadb
systemctl stop mariadb
# 修改配置文件
vim /etc/my.cnf
# 修改内容
# 解释:server-id服务的唯一标识(主从之间都必须不同);log-bin启动二进制日志名称为mysql-bin
[mysqld]
server-id=1 # 跟从服务器不同即可
log-bin=qishi-logbin # 日志名称,随需要换
# 重启mariadb
systemctl start mariadb
(3) 还是在主库上操作(创建主从同步用户)
# 1.新建用于主从同步的用户haonan,允许登录的从库是"%"所有,也可以是'10.0.3.85'
create user 'haonan'@'%' identified by 'hn666';
# 2.给从库账号授权,说明给haonan从库复制的权限,在10.0.3.85机器上复制
grant replication slave on *.* to 'haonan'@'%';
# 3.实现对主数据库锁表只读,防止数据写入,数据复制失败
# 锁表的目的就是为了在主从复制完成之前,保持两台数据库的数据一致
flush table with read lock;
# 4.检查主库的状态, 并记录下日志文件的名字,和位置
MariaDB [(none)]> show master status;
# 5.锁表后,要单独再打开一个SSH窗口,导出数据库的所有数据,也可以退出数据库
mysqldump -uroot -p --all-databases > /opt/master.sql
# 6.确保数据导出后,没有数据插入,完毕再查看主库状态
show master status;
(4) 在从库上进行操作
# 1.将备份导出的数据scp至Slave数据库
scp /opt/master.sql root@10.0.3.85:/opt/
# 2.在从库上导入数据
source /opt/master.sql
# 3.修改Slave从库的/etc/my.cnf,写入
[mysqld]
server-id=3
read-only=true # 用户只读模式
# 4.重启数据库
systemctl restart mariadb
# 5 配置复制的参数,Slave从库连接Master主库的配置
mysql > change master to master_host='10.0.3.82', # 这是连接的主数据库ip
master_user='haonan', # 这是用户名
master_password='hn666', # 这是数据库的密码
master_log_file='qishi-logbin.000001', # 要对应主数据库File的名称
master_log_pos=471; # 要对应主数据库Position的值
# 6.启动从库的同步开关,测试主从复制的情况
start slave;
# 7.查看复制状态
show slave status\G;
主从配置:都是yes即表示配置正确
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
(5) 再回到主库上进行操作
# 1.最后再给主库解锁
unlock tables;
# 2. 最后在主库上刷新授权表
flush privileges;
(6) 在从库上登录普通用户,尝试写入数据会报以下错误, 说明从库只读
注意使用普通用户主从同步即可达到只读的效果, 如果用root用户,无法达到readonly,这是一个坑
创建用户和授权用户
1.新建用于主从同步的用户haonan,允许登录的从库是"%"所有,也可以是'10.0.3.85'
create user 'haonan'@'%' identified by 'hn666';
# 2.给从库账号授权,说明给haonan从库复制的权限,在10.0.3.85机器上复制
grant replication slave on *.* to 'haonan'@'%';
导出数据
mysqldump -uroot -p --all-databases > /opt/master.sql
导入数据库
source /opt/master.sql
注意:复制文件
从当前主机复制给另一台主机
scp /文件路径/ root@对方的ip地址:/路径
从当前主机去到另一台主机复制
scp root@对方ip地址:/对方路径 /路径
取消主从复制
在从库:
mysql>stop slave;
# QueryOK, 0 rowsaffected (0,00 sec)
mysql>reset slave all;
# QueryOK, 0 rowsaffected (0,04 sec)
mysql> show slave status\G
# Emptyset (0,00 sec)
# mariadb数据库
# 默认安装的数据路径
# /var/lib/mysql
#配置文件
# /etc/my.conf