mysql 数据库主从配置教程

1. 安装数据库服务器
   1. 服务器信息

需要在两台服务器上都安装JDK和mysql

序号	IP地址	需开放端口	备注
1	10.30.170.27	3306	Mysql主机（master）
2	10.30.170.28	3306	Mysql从机（slave）

 

使用以下命令开发防火墙端口

#查看已开启的端口

$ firewall-cmd --list-ports

#开启端口

$ firewall-cmd --zone=public --add-port=3306/tcp --permanent  

#重启加载防火墙配置

$ firewall-cmd --reload

1. 1. 安装MySql
      1. 上传并解压安装包

将安装包中的mysql-5.7.29-1.el7.x86_64.rpm-bundle.tar上传至服务器的/home/mysql目录下，并使用以下命令解压，得到多个rpm包

$ tar -xvf mysql-5.7.29-1.el7.x86_64.rpm-bundle.tar

1. 1. 1. 卸载旧的安装包

查看旧的依赖包:

$ rpm -qa | grep mysql 

卸载mysql旧的依赖包:

$ rpm -e --nodeps xxx

安装依赖包：

$ yum install numactl libaio perl –y

1. 1. 1. 执行安装步骤
         1. 执行安装命令

以此执行以下命令安装mysql，因为包之间有依赖关系，所以顺序不能搞错

$ rpm -ivh mysql-community-common-5.7.29-1.el7.x86_64.rpm --force --nodeps

$ rpm -ivh mysql-community-libs-5.7.29-1.el7.x86_64.rpm --force --nodeps

$ rpm -ivh mysql-community-client-5.7.29-1.el7.x86_64.rpm --force --nodeps

$ rpm -ivh mysql-community-server-5.7.29-1.el7.x86_64.rpm --force --nodeps

1. 1. 1. 1. 执行初始化命令

执行命令进入usr/bin目录：

$ cd /usr/bin

然后执行初始化命令：

$ mysqld --initialize --user=mysql

1. 1. 1. 1. 获取默认密码

执行查看mysql日志命令:

$ vi /var/log/mysqld.log

查找到内容root@localhost：后面的内容为root用户的随机密码，大致为：

A temporary password is generated for root@localhost: ueoH8BxXJ+qs

1. 1. 1. 1. 启动并登录mysql修改默认密码

执行mysql启动命令启动mysql:

$ service mysqld start

执行mysql命令行登录命令：

$ mysql -u root -p

输入上面得到的随机密码进行登录

修改密码：

$ alter user 'root'@'localhost' identified by 'ctzqbj601108$';

修改之后使用退出命令退出：

$ exit;

1. 1. 1. 1. 创建远程连接用户：

执行mysql命令行登录命令:

$ mysql -u root -p

输入修改后的密码进行登录，登录成功后执行命令创建用户：

$ CREATE USER 'founder'@'localhost' IDENTIFIED BY 'founder123';

执行命令赋予远程连接权限：

为founder用户授权本地访问：

$ grant all privileges on *.* to founder@localhost identified by 'founder123' with grant option;

为founder用户授权远程访问：

$ grant all privileges on *.* to founder@"%" identified by 'founder123' with grant option;

为root用户授权远程访问：

$ grant all privileges on *.* to root@"%" identified by 'founder123' with grant option;

最后使用退出命令退出：

$ exit;

1. 1. 1. 修改mysql参数

修改mysql的参数需要修改mysql的配置my.cnf，使用命令：

$ vi /etc/my.cnf

在[mysqld]下添加配置

#设置大小写不敏感

lower_case_table_names= 1

#配置字符集

character-set-server= utf8mb4

collation-server=utf8mb4_unicode_ci

skip-character-set-client-handshake=1

 

innodb_buffer_pool_size = 16G

#sort_buffer_size = 4M

sort_buffer_size = 8M

#join_buffer_size = 2M

join_buffer_size = 8M

#thread_cache_size = 512

thread_cache_size = 1024

query_cache_size = 32M

query_cache_limit = 2M

innodb_autoextend_increment = 256

max_allowed_packet = 100M

 

read_buffer_size = 8M

#max_connections = 768

thread_stack = 1M

#table_cache = 2048

 

#innodb_additional_mem_pool_size = 20M

innodb_read_io_threads = 6

innodb_write_io_threads = 6

innodb_log_buffer_size = 48M

 

sql-mode="STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

 

配置完成使用以下命令重启mysql：

$ service mysqld restart

• 1. 配置mysql主从复制
     1. 配置mysql主机（10.30.170.27）
• 第一步：修改主库的配置:

$ vi /etc/my.cnf

在my.cnf配置文件中添加如下配置：

init_connect='SET collation_connection = utf8mb4_unicode_ci'

init_connect='SET NAMES utf8mb4'

skip-character-set-client-handshake=1

#设置唯一id,这里用的ip最后一位

server-id=210

#binlog日志格式，mysql默认采用ROW，建议使用mixed

binlog_format=MIXED

#binlog日志文件

log-bin=mysql-bin

#binlog过期清理时间

expire_logs_days=20

#设置不需要同步的库，多个设置多行

binlog-ignore-db=mysql

binlog-ignore-db=information_schema

binlog-ignore-db=performance_schema

binlog-ignore-db=sys

 

[client]

default-character-set=utf8mb4

 

[mysql]

default-character-set=utf8mb4

 

 

配置好以上配置后重启mysql

$ service mysqld restart

 

需要注意的点：

1.为了兼容emoji等特殊字节符，配置中使用utf8mb4编码

2.与主从相关的配置在整个配置的最下边，主库和从库的server-id必须不同

3.binlog-ignore-db设置不需要同步的库

4. binlog_format=MIXED  binlog日志格式，mysql5.7以后默认采用ROW，建议使用mixed，效果更高

5.expire_logs_days=20 binlog过期清理时间，binlog会不断增加，如果不设置清理时间最后会暂满磁盘空间。

• 第二步：创建用于主从同步的账户

在主库创建专职用于主从同步的用户,并给该用户赋予FILE权限和主从同步权限，注意赋权时是给从库的IP授权，允许从从库ip使用该用户来访问主库。

1. 执行mysql登录命令行登录 

$ mysql -uroot -p

2. 登录后使用以下命令创建用于主从同步的用户，必须指定’%’远程访问且指定密码，注意mysql5.7版本之后要求密码必须含有大小写英文，符号和数字

$ create user 'sync'@'%' identified by 'Sync!0000';

3. 赋予FILE权限，允许从从库ip访问主库

$ grant FILE on *.* to 'sync'@'10.30.170.28' identified by 'Sync!0000'; 

4. 赋予主从同步权限

$ grant replication slave on *.* to 'sync'@'10.30.170.28' identified by 'Sync!0000';

5.刷新权限信息，并退出

$ flush privileges;

$ exit;

6.重启mysql

$ service mysqld restart

• 第三步：查看主库状态，记录必要信息

1. 执行mysql登录命令行登录 

$ mysql -uroot -p

2.使用如下命令记录File和Position对应的信息，如图中的是mysql-bin.000004和154，这两个值标识主库日志的文件和读取位置信息，在从库设置复制时会用到这两个值。

$ show master status;

 

• 1. 1. 配置mysql从机（10.30.170.28）
• 第一步：修改从库的配置:

$ vi /etc/my.cnf

在my.cnf配置文件中添加如下配置：

init_connect='SET collation_connection = utf8mb4_unicode_ci'

init_connect='SET NAMES utf8mb4'

skip-character-set-client-handshake

#设置唯一id

server-id=210

#binlog日志格式，mysql默认采用ROW，建议使用mixed

binlog_format=MIXED

#binlog日志文件

log-bin=mysql-bin

#binlog过期清理时间

expire_logs_days=20

#设置不需要同步的库，多个设置多行

replicate-ignore-db=mysql

replicate-ignore-db=information_schema

replicate-ignore-db=performance_schema

replicate-ignore-db=sys

#从站接收的所有事务（甚至来自多个源）都写入二进制日志中

log-slave-updates=ON

#忽略所有错误，继续复制

slave-skip-errors=all

 

[client]

default-character-set=utf8mb4

 

[mysql]

default-character-set=utf8mb4

 

 

配置好以上配置后重启mysql

$ service mysqld restart

 

需要注意的点：

1. 注意事项和主库配置相同，但使用的是replicate-ignore-db标记出不需要同步的库

2. log-slave-updates=ON 从站接收的所有事务（甚至来自多个源）都写入二进制日志中。

3. slave-skip-errors=all 忽略所有错误，继续复制

• 第二步：配置同步账号

在主库中已经创建了专职用于主从同步的用户,并给该用户赋予FILE权限和主从同步权限，这一步需要设置允许从从库ip使用在主库创建的用户来访问主库。

1. 执行mysql登录命令行登录

$ mysql -uroot -p

2. 停止从库的执行线程

$ stop slave;

1. 设置主从复制，这是最关键的一步，将主库ip，同步账号的用户名密码，在主库使用show master status得到的file、position写入

$ change master to MASTER_HOST='10.30.170.27', MASTER_USER='sync',MASTER_PASSWORD='Sync!0000',MASTER_LOG_FILE ='mysql-bin.000004',MASTER_LOG_POS=154;

4. 启动从库的执行线程

$ start slave;

5. 查看同步状态,大致如图所示，出现Slave_IO_RUNNING:Yes和Slave_SQL_Running：Yes 说明主从同步已经搭建完成

$ show slave status \G;

• 解决问题

有时会出现，Slave_IO_RUNNING:No和Slave_SQL_Running：No的情况，大概的原因有三种：

1. 网络不通，或者防火墙没有打开3306端口
2. 在配置slave同步时因为slave访问master没有权限导致，可能是创建同步账号授权出了问题
3. 设置在主库使用show master status得到的file、position发生了变更，需要重新查看最新的值，然后重新设置主从复制的file和position。