MySQL主从同步

CentOS7 安装MySQL

下载rpm并安装

1
2
wget https://dev.mysql.com/get/mysql57-community-release-el7-9.noarch.rpm
rpm -ivh mysql57-community-release-el7-9.noarch.rpm

使用yum命令即可完成安装

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
# 安装
yum install mysql-server

# 启动msyql:
systemctl start mysqld

# 获取安装时的临时密码(在第一次登录时就是用这个密码):
grep 'temporary password' /var/log/mysqld.log

# 登录
mysql -u root -p

# 修改密码
ALTER USER 'root'@'localhost' IDENTIFIED BY '123456';

#刷新
flush privileges;

七、其他配置

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34

# 设置安全选项:
mysql_secure_installation

# 关闭MySQL
systemctl stop mysqld

# 重启MySQL
systemctl restart mysqld

# 查看MySQL运行状态
systemctl status mysqld

# 设置开机启动
systemctl enable mysqld

# 关闭开机启动
systemctl disable mysqld

# 配置默认编码为utf8:
vi /etc/my.cnf #添加 [mysqld] character_set_server=utf8 init_connect='SET NAMES utf8'

# 其他默认配置文件路径:
#配置文件:
/etc/my.cnf
#日志文件:
/var/log//var/log/mysqld.log
#服务启动脚本:
/usr/lib/systemd/system/mysqld.service
#socket文件:
/var/run/mysqld/mysqld.pid

# 查看版本
select version();

准备:
两台MySQL服务器:
1、Master:192.168.172.110
2、Slave:192.168.172.111
端口都是3306

配置详解:
一、主库配置:
1、编辑配置文件:

1
2
3
4
5
6
7
8
9
10
11
$ vim /etc/my.cnf

server-id=110 #设置主服务器的ID(不能和别的服务器重复,建议使用ip的最后一段)
innodb_flush_log_at_trx_commit=2 #可以配置的值:0/1/2; 0、效率最高,最不安全;1、最安全,但是效率最低;2、安全和效率平衡的取舍,在服务器系统挂掉的情况下会丢失数据;
sync_binlog=1 #,值可设置 1、500、1000;可自己根据测试性能配置
log-bin=mysql-bin #binlog日志文件名
binlog-ignore-db=mysql # 表示不同步mysql库
binlog-ignore-db=information_schema # 表示不同步information_schema库
binlog-ignore-db=performance_schema
binlog-ignore-db=sys
binlog-do-db=xxxx # 这个表示只同步某个库 (如果没有此项,表示同步所有的库)

2、创建用于主从同步的账户:

1
2
3
4
5
6
#登录MySQL 
$ mysql -u root -p
mysql> create user 'sync'@'%' identified by 'Sync@0000'; # 5.7要求密码必须含有大小写英文,符号和数字
mysql> grant FILe on *.* to 'sync'@'192.168.172.111' identified by 'Sync@0000'; #赋予FILE权限,允许从从库ip访问主库
mysql> grant replication slave on *.* to 'sync'@'192.168.172.111' identified by 'Sync@0000'; #赋予主从同步权限
mysql> flush privileges;

3、重启MySQL,使my.cnf 配置生效;查看主库状态:

1
2
3
4
5
6
7
8
9
$ service mysqld restart #重启MySQL
mysql -u root -p
mysql> show master status; #查看主库的状态 file,position 这两个值很有用,记一下;需要放到slave配置中
+--------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+--------------------+----------+--------------+------------------+-------------------+
| mysql-bin.00001 | 156 | xxxx | | |
+--------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

二、从库配置:
1、编辑配置文件:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
$ vim /etc/my.cnf

server-id=111
#log-bin=mysql-bin #从库提高性能可以不开bin-log日志
replicate-ignore-db=mysql #配置不需要复制的库mysql
replicate-ignore-db=information_schema
replicate-ignore-db=performance_schema
replicate-ignore-db=sys
replicate_do_db=python #标记出需要同步的数据库名,在多从配置时可以按需配置
innodb_flush_log_at_trx_commit=2 # 可以配置的值:0/1/2; 0、效率最高,最不安全;1、最安全,但是效率最低;2、安全和效率平衡的取舍,在服务器系统挂掉的情况下会丢失数据;
sync_binlog=1000 # 每进行n次事务提交之后,MySQL将binlog_cache中的数据强制写入磁盘。
slave_parallel_workers=4 #根据实际情况决定开启多少个线程用于主从复制
slave_parallel_type=logical_clock #基于组提交的并行复制方式
master_info_repository=TABLE
relay_log_info_repository=TABLE
relay_log_recovery=ON

2、配置完成后,重启从库的MySQL;

1
2
3
4
5
6
$ service mysqld restart #重启MySQL
$ mysql -u root -p #登录mysql
mysql> stop slave; #关闭从库
mysql> change master to master_host='192.168.172.110', master_user='sync' ,master_password='Sync@0000', master_log_file='mysql-bin.00001' ,master_log_pos=156; #配置主库信息
mysql> start slave; #开启从库
mysql> show slave status \G; #Slave_IO_Running,Slave_SQL_Running 都为Yes的时候表示配置成功

3、验证主从:
可以在主库上对数据进行操作,再在从库上刷新是否同步;

MySQL的主从配置很简单

Got fatal error 1236 from master when reading data from binary log: ‘Could not find first log file name in binary log index file’系列一:
主库添加log-bin-index 参数后,从库报这个错误:Got fatal error 1236 from master when reading data from binary log: ‘Could not find first log file name in binary log index file’

Got fatal error 1236 from master when reading data from binary log: ‘could not find next log’

可以

1
2
3
stop slave;
reset slave;
start slave;

常用命令

1
2
3
4
5
6
7
8
9
10
11
12
13
14
# 创建数据库和用户
create database databasename default charset utf8 collate utf8_general_ci;
create user 'username'@'%' identified by 'password';
grant all privileges on databasename.* to 'username'@'%'
flush privileges;

# 替换文字
语法:UPDATE 表名 SET 字段名=replace(字段名, '被替换字符串', '新的字符串') ;
示例:UPDATE tb_user SET name=replace( name, '\'', '') ;

# 复制表结构
CREATE TABLE targetTable LIKE sourceTable;
# 复制表数据
INSERT INTO targetTable SELECT * FROM sourceTable;