技术交流QQ群:①185473046   ②190706903   ③203744115   网站地图
登录

下次自动登录
现在的位置: 首页主从架构>正文
CentOS 7.x下MySQL数据库主从同步配置
2022年06月30日 主从架构 暂无评论 ⁄ 被围观 2,653次+

说明:

操作系统:CentOS 7.x 64位

MySQL数据库版本:mysql-5.7.38-linux-glibc2.12-x86_64.tar.gz

MySQL主服务器:192.168.21.128

MySQL从服务器:192.168.21.129

准备篇:

说明:在两台MySQL服务器192.168.21.128和192.168.21.129上分别进行如下操作

一、防火墙配置

CentOS 7.x 默认使用的是firewall作为防火墙,这里改为iptables防火墙。

1、关闭firewall:

systemctl stop firewalld.service #停止firewall

systemctl disable firewalld.service #禁止firewall开机启动

systemctl mask firewalld

systemctl stop firewalld

yum remove firewalld

2、安装iptables防火墙

yum install iptables-services #安装

vi /etc/sysconfig/iptables #编辑防火墙配置文件

# sample configuration for iptables service

# you can edit this manually or use system-config-firewall

# please do not ask us to add additional ports/services to this default configuration

*filter

:INPUT ACCEPT [0:0]

:FORWARD ACCEPT [0:0]

:OUTPUT ACCEPT [0:0]

-A INPUT -m state --state RELATED,ESTABLISHED -j ACCEPT

-A INPUT -p icmp -j ACCEPT

-A INPUT -i lo -j ACCEPT

-A INPUT -p tcp -m state --state NEW -m tcp --dport 22 -j ACCEPT

-A INPUT -p tcp -m state --state NEW -m tcp --dport 3306 -j ACCEPT

-A INPUT -j REJECT --reject-with icmp-host-prohibited

-A FORWARD -j REJECT --reject-with icmp-host-prohibited

COMMIT

:wq! #保存退出

systemctl restart iptables.service #最后重启防火墙使配置生效

systemctl enable iptables.service #设置防火墙开机启动

/usr/libexec/iptables/iptables.init restart #重启防火墙

二、关闭SELINUX

vi /etc/selinux/config

#SELINUX=enforcing #注释掉

#SELINUXTYPE=targeted #注释掉

SELINUX=disabled #增加

:wq! #保存退出

setenforce 0 #使配置立即生效

三、修改主机名称

#设置主机名为mysql-master

hostname mysql-master

hostnamectl set-hostname mysql-master

vi /etc/hostname #编辑配置文件

mysql-master #修改localhost.localdomain为mysql-master

:wq! #保存退出

vi /etc/hosts #编辑配置文件

127.0.0.1 localhost mysql-master #修改localhost.localdomain为mysql-master

#从服务器执行相同的操作,把主机名称分别修改为服务器角色对应的名称mysql-slave

四、下载软件包

MySQL二进制版本下载地址:https://cdn.mysql.com/archives/mysql-5.7/mysql-5.7.38-linux-glibc2.12-x86_64.tar.gz

上传mysql-5.7.38-linux-glibc2.12-x86_64.tar.gz安装包到/usr/local/src目录下

五、安装mysql依赖包(初始化mysql数据库需要)

#yum源安装

yum install libaio-devel

#rpm包安装

#下载地址

cd /usr/local/src

https://mirrors.163.com/centos-vault/7.6.1810/os/x86_64/Packages/libaio-0.3.109-13.el7.x86_64.rpm

https://mirrors.163.com/centos-vault/7.6.1810/os/x86_64/Packages/libaio-devel-0.3.109-13.el7.x86_64.rpm

cd /usr/local/src

rpm -ivh libaio-0.3.109-13.el7.x86_64.rpm

rpm -ivh libaio-devel-0.3.109-13.el7.x86_64.rpm

安装篇

说明:在两台MySQL服务器192.168.21.128和192.168.21.129上分别进行如下操作

备注:作为主从服务器的MySQL版本必须一致

一、安装mysql

1、创建mysql运行用户和组

groupadd mysql

useradd -g mysql mysql -s /bin/false

2、创建mysql安装目录和mysql数据库存放目录

mkdir -p /data/server/mysql #mysql安装目录

mkdir -p /data/server/mysql/data #mysql数据库存放目录

3、设置目录权限

chown -R mysql:mysql /data/server/mysql

chown -R mysql:mysql /data/server/mysql/data

4、安装mysql

#解压二进制版本mysql安装包到安装目录

cd  /usr/local/src/

tar zxvf mysql-5.7.38-linux-glibc2.12-x86_64.tar.gz -C /data/server/mysql --strip-components 1

二、设置my.cnf配置文件

vi /data/server/mysql/my.cnf #编辑添加

# For advice on how to change settings please see

# http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html

# *** DO NOT EDIT THIS FILE. It's a template which will be copied to the

# *** default location during install, and will be replaced if you

# *** upgrade to a newer version of MySQL.

[client]

port = 3306

socket=/data/server/mysql/data/mysql.sock

[mysql]

port = 3306

socket = /data/server/mysql/data/mysql.sock

[mysqld]

# Remove leading # and set to the amount of RAM for the most important data

# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.

# innodb_buffer_pool_size = 128M

# Remove leading # to turn on a very important data integrity option: logging

# changes to the binary log between backups.

# log_bin

# These are commonly set, remove the # and set as required.

user=mysql

port = 3306

basedir = /data/server/mysql

datadir = /data/server/mysql/data

socket = /data/server/mysql/data/mysql.sock

pid-file= /data/server/mysql/data/mysql.pid

server_id = 1

character-set-server = utf8mb4

default-storage-engine = InnoDB

tmpdir = /tmp

max_connections = 1000

innodb_buffer_pool_size = 16GB #默认为128MB,mysql专用服务器建议设置为内存的70%

innodb_buffer_pool_chunk_size = 2GB

innodb_buffer_pool_instances = 8 #最大值64

# Remove leading # to set options mainly useful for reporting servers.

# The server defaults are faster for transactions and fast SELECTs.

# Adjust sizes as needed, experiment to find the optimal values.

# join_buffer_size = 128M

# sort_buffer_size = 2M

# read_rnd_buffer_size = 2M

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

default_authentication_plugin=mysql_native_password

explicit_defaults_for_timestamp=true

[mysqld_safe]

log-error = /data/server/mysql/data/error.log

:wq! #保存退出

三、添加mysql系统环境变量

vi /etc/profile #把mysql服务加入系统环境变量:在最后添加下面这一行

export PATH=$PATH:/data/server/mysql/bin

:wq! #保存退出

source /etc/profile #使配置立即生效

四、初始化mysql数据库

/data/server/mysql/bin/mysqld --defaults-file=/data/server/mysql/my.cnf --user=mysql --initialize-insecure --basedir=/data/server/mysql --datadir=/data/server/mysql/data --socket=/data/server/mysql/data/mysql.sock --pid-file=/data/server/mysql/data/mysql.pid

[root@mysql-slave mysql]# /data/server/mysql/bin/mysqld --defaults-file=/data/server/mysql/my.cnf --user=mysql --initialize-insecure --basedir=/data/server/mysql --datadir=/data/server/mysql/data --socket=/data/server/mysql/data/mysql.sock --pid-file=/data/server/mysql/data/mysql.pid

2022-08-20T09:50:56.536905Z 0 [Warning] 'NO_ZERO_DATE', 'NO_ZERO_IN_DATE' and 'ERROR_FOR_DIVISION_BY_ZERO' sql modes should be used with strict mode. They will be merged with strict mode in a future release.

2022-08-20T09:50:56.536961Z 0 [Warning] 'NO_AUTO_CREATE_USER' sql mode was not set.

2022-08-20T09:50:56.775855Z 0 [Warning] InnoDB: New log files created, LSN=45790

2022-08-20T09:50:56.827826Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.

2022-08-20T09:50:56.892979Z 0 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: 969fc0b2-206d-11ed-a4d5-000c29ae577c.

2022-08-20T09:50:56.894562Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.

2022-08-20T09:50:57.079229Z 0 [Warning] A deprecated TLS version TLSv1 is enabled. Please use TLSv1.2 or higher.

2022-08-20T09:50:57.079243Z 0 [Warning] A deprecated TLS version TLSv1.1 is enabled. Please use TLSv1.2 or higher.

2022-08-20T09:50:57.079691Z 0 [Warning] CA certificate ca.pem is self signed.

2022-08-20T09:50:57.248135Z 1 [Warning] root@localhost is created with an empty password ! Please consider switching off the --initialize-insecure option.

五、启动mysql5.7.x数据库

/data/server/mysql/bin/mysqld_safe --defaults-file=/data/server/mysql/my.cnf --user=mysql --port=3306 --basedir=/data/server/mysql --datadir=/data/server/mysql/data --socket=/data/server/mysql/data/mysql.sock --pid-file=/data/server/mysql/data/mysql.pid &

六、添加软连接

ln -s /data/server/mysql/data/mysql.sock /tmp/mysql.sock

七、设置MySQL管理员root密码,根据提示设置密码

/data/server/mysql/bin/mysql_secure_installation --socket=/data/server/mysql/data/mysql.sock

y使用密码验证插件

选择密码规则(一般选择第二项:1 = MEDIUM)

LOW Length >= 8 #长度大于等于8

MEDIUM Length >= 8, numeric, mixed case, and special characters #长度大于等于8,数字、大小写字母、特殊符号

STRONG Length >= 8, numeric, mixed case, special characters and dictionary file #长度大于等于8,数字、大小写字母、特殊符号和字典文件(慎选!)

Please enter 0 = LOW, 1 = MEDIUM and 2 = STRONG: 1

设置新密码:12345678Abc++

y删除匿名用户

y禁止root远程登录

y删除测试数据库

y重新加载权限表

#关闭mysql5.7.x数据库

/data/server/mysql/bin/mysqladmin -uroot -S /data/server/mysql/data/mysql.sock shutdown -p

#根据提示输入上面设置的密码即可关闭数据库

八、设置mysql启动脚本

cp /data/server/mysql/support-files/mysql.server /data/server/mysql/mysql.sh

vi /data/server/mysql/mysql.sh #编辑修改

basedir=/data/server/mysql #MySQL程序安装路径

datadir=/data/server/mysql/data #MySQl数据库存放目录

conf=/data/server/mysql/my.cnf #mysql配置文件my.cnf路径

:wq! #保存退出

#添加执行权限

chmod +x /data/server/mysql/mysql.sh

#添加开机启动

vi /etc/rc.d/rc.local

/bin/sh /data/server/mysql/mysql.sh start

:wq! #保存退出

#默认/etc/rc.local没有执行权限,需要手动添加执行权限

chmod +x /etc/rc.d/rc.local

sh /data/server/mysql/mysql.sh start #启动mysql

配置篇

一、配置MySQL主服务器(192.168.21.128)

1、创建mysql数据库和用户

1.1

#创建数据库和用户

数据库名:mydb

数据库用户名:myuser

数据库密码:12345678Abc++

1.2

#创建mysql数据库管理员

mysql数据库管理员用户名:admin

密码:12345678Abc++

进入mysql5.7.x控制台

/data/server/mysql/bin/mysql -uroot -p -S /data/server/mysql/data/mysql.sock

Create DATABASE IF NOT EXISTS mydb default charset utf8mb4 COLLATE utf8mb4_general_ci; #创建数据库mydb

GRANT ALL PRIVILEGES ON mydb.* TO myuser@'192.168.21.%' IDENTIFIED BY '12345678Abc++' WITH GRANT OPTION; #创建用户myuser并授权

flush privileges; #刷新系统授权表

GRANT ALL PRIVILEGES ON *.* TO admin@'127.0.0.1' IDENTIFIED BY '12345678Abc++' WITH GRANT OPTION; #创建mysql数据库管理员admin并授权

GRANT ALL PRIVILEGES ON *.* TO admin@'localhost' IDENTIFIED BY '12345678Abc++' WITH GRANT OPTION; #创建mysql数据库管理员admin并授权

flush privileges; #刷新系统授权表

2、创建mysql主从同步账号并授权

#创建并授权用户repl只能从192.168.21.129这个IP访问主服务器192.168.21.128上面的数据库,并且只具有数据库备份的权限

grant replication slave on *.* to 'repl'@'192.168.21.129' identified by '12345678Abc++' with grant option;

flush privileges; #刷新系统授权表

exit; #退出控制台

3、导出主库中的mydb数据库

#在MySQL主服务器进行操作,导出数据库lst到/tmp/mydb.sql

/data/server/mysql/bin/mysqldump -u root -p --default-character-set=utf8mb4 --opt -Q -R --skip-lock-tables mydb > /tmp/mydb.sql

#把/tmp目录下的mydb.sql 数据库文件上传到MySQL从服务器的/tmp目录下面

scp /tmp/mydb.sql root@192.168.21.129:/tmp

备注:在导出之前可以先进入MySQL控制台执行下面命令

flush tables with read lock; #数据库只读锁定命令,防止导出数据库的时候有数据写入

unlock tables; #解除锁定

4、编辑修改MySQL主服务器(192.168.21.128)的my.cnf文件

vi /data/server/mysql/my.cnf

server-id=1 #设置服务器id,为1表示主服务器,注意:如果原来的配置文件中已经有这一行,就不用再添加了。

log-bin=mysql-bin #启动MySQ二进制日志系统,注意:如果原来的配置文件中已经有这一行,就不用再添加了。

binlog-do-db=mydb #同步的数据库名,如果有多个数据库,可重复此参数,每个数据库一行

binlog-ignore-db=mysql #不同步mysql系统数据库

:wq! #保存退出

sh /data/server/mysql/mysql.sh restart #重启MySQL

mysql -u root -p #进入mysql控制台

show variables like 'server_id'; #查看server-id的值是否为1

mysql> show variables like 'server_id';

+---------------+-------+

| Variable_name | Value |

+---------------+-------+

| server_id | 1 |

+---------------+-------+

1 row in set (0.00 sec)

mysql>

show master status; #查看主服务器,出现以下类似信息

mysql> show master status;

+------------------+----------+--------------+------------------+-------------------+

| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |

+------------------+----------+--------------+------------------+-------------------+

| mysql-bin.000001 | 154 | mydb | mysql | |

+------------------+----------+--------------+------------------+-------------------+

1 row in set (0.00 sec)

mysql>

注意:这里记住File的值:mysql-bin.000001和Position的值:154,后面会用到。

二、配置MySQL从服务器(192.168.21.129)

1、导入数据库文件到MySQL从服务器

mysql -u root -p #进入从服务器MySQL控制台

Create DATABASE IF NOT EXISTS mydb default charset utf8mb4 COLLATE utf8mb4_general_ci;  #创建数据库

use mydb #进入数据库

source /tmp/mydb.sql #导入备份文件到数据库

exit;  #退出控制台

2、编辑修改MySQL从服务器(192.168.21.129)的my.cnf文件

vi /data/server/mysql/my.cnf #编辑配置文件,在[mysqld]部分添加下面内容

server-id=2 #设置服务器id,修改其值为2,表示为从数据库

log-bin=mysql-bin #启动MySQ二进制日志系统,注意:如果原来的配置文件中已经有这一行,就不用再添加了。

replicate-do-db=mydb #需要同步的数据库名,如果有多个数据库,可重复此参数,每个数据库一行

replicate-ignore-db=mysql #不同步mysql系统数据库

read_only #设置数据库只读

:wq! #保存退出

sh /data/server/mysql/mysql.sh restart #重启MySQL

3、开启mysql主从同步

mysql -u root -p #进入mysql控制台

show variables like 'server_id'; #查看server-id的值是否为2

stop slave; #停止slave同步进程

#执行主从同步操作

change master to master_host='192.168.21.128',master_user='repl',master_password='12345678Abc++',master_log_file='mysql-bin.000001' ,master_log_pos=154;

start slave; #开启slave同步进程

SHOW SLAVE STATUS\G #查看slave同步信息,出现以下内容

mysql> start slave;

Query OK, 0 rows affected (0.00 sec)

mysql> SHOW SLAVE STATUS\G

*************************** 1. row ***************************

Slave_IO_State: Waiting for master to send event

Master_Host: 192.168.21.128

Master_User: repl

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: mysql-bin.000001

Read_Master_Log_Pos: 154

Relay_Log_File: mysql-slave-relay-bin.000002

Relay_Log_Pos: 320

Relay_Master_Log_File: mysql-bin.000001

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

Replicate_Do_DB: mydb

Replicate_Ignore_DB: mysql

Replicate_Do_Table:

Replicate_Ignore_Table:

Replicate_Wild_Do_Table:

Replicate_Wild_Ignore_Table:

Last_Errno: 0

Last_Error:

Skip_Counter: 0

Exec_Master_Log_Pos: 154

Relay_Log_Space: 533

Until_Condition: None

Until_Log_File:

Until_Log_Pos: 0

Master_SSL_Allowed: No

Master_SSL_CA_File:

Master_SSL_CA_Path:

Master_SSL_Cert:

Master_SSL_Cipher:

Master_SSL_Key:

Seconds_Behind_Master: 0

Master_SSL_Verify_Server_Cert: No

Last_IO_Errno: 0

Last_IO_Error:

Last_SQL_Errno: 0

Last_SQL_Error:

Replicate_Ignore_Server_Ids:

Master_Server_Id: 1

Master_UUID: d6f0a1e7-2061-11ed-8594-000c292ccd74

Master_Info_File: /data/server/mysql/data/master.info

SQL_Delay: 0

SQL_Remaining_Delay: NULL

Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates

Master_Retry_Count: 86400

Master_Bind:

Last_IO_Error_Timestamp:

Last_SQL_Error_Timestamp:

Master_SSL_Crl:

Master_SSL_Crlpath:

Retrieved_Gtid_Set:

Executed_Gtid_Set:

Auto_Position: 0

Replicate_Rewrite_DB:

Channel_Name:

Master_TLS_Version:

1 row in set (0.00 sec)

mysql>

注意查看:

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

以上这两个参数的值为Yes,即说明配置成功!

测试篇

1、进入MySQL主服务器(192.168.21.128)

mysql -u root -p #进入MySQL控制台

use mydb #进入数据库

CREATE TABLE test1 ( id int not null primary key,name char(20) ); #创建test表

2、进入MySQL从服务器

mysql -u root -p #进入MySQL控制台

use mydb #进入数据库

show tables; #查看mydb表结构,会看到有一个新建的表test1,表示数据库同步成功

扩展阅读:可以在MySQL主服务器上绑定一个VIP,对外提供这个VIP的ip地址进行连接

#绑定VIP

/usr/bin/sudo /sbin/ip addr add 192.168.21.130/24 dev ens33 label ens33:0

#添加到开机启动

vi /etc/rc.d/rc.local

/usr/bin/sudo /sbin/ip addr add 192.168.21.130/24 dev ens33 label ens33:0

:wq! #保存退出

#添加执行权限

chmod +x /etc/rc.d/rc.local

至此,Linux下MySQL数据库主从同步配置完成。

     

  系统运维技术交流QQ群:①185473046 系统运维技术交流□Ⅰ ②190706903 系统运维技术交流™Ⅱ ③203744115 系统运维技术交流™Ⅲ

给我留言

您必须 [ 登录 ] 才能发表留言!



Copyright© 2011-2024 系统运维 All rights reserved
版权声明:本站所有文章均为作者原创内容,如需转载,请注明出处及原文链接
陕ICP备11001040号-3