mysql主从配置~

最新推荐文章于 2024-09-13 16:04:09 发布

平凡就是福

最新推荐文章于 2024-09-13 16:04:09 发布

阅读量146

点赞数

本文链接：https://blog.csdn.net/MengHuanXinZuo/article/details/106387741

版权

必要性：
- 用户体验：在项目中，我们可能会遇到数据库宕机。通过排查原因，会使系统在一段时间内不能访问，大大降低了用户体验。若是我们配置主从数据库，那么主数据库自然过度到从数据库，使得用户察觉不到数据库的宕机，提高系统的稳健性。
- 系统负荷：我们在项目中也可以通过配置主从服务器来进行读写分离。主数据库做为写的操作，而从服务器作为读的操作，大大降低一台数据库的压力。
实现原理性：

mysql主从同步其实是一个异步复制过程，实现复制需要在master上开启bin-log日志功能。整个实现需要三个步骤，在master开启IO线程，slave开启IO线程以及SQL线程。
- 从服务器（slave端）：slave端开启IO线程通过授权的用户连接上master端，并对master端指定的文件进行请求bin-log日志内容。
- 主服务器（master端）：master端IO线程通过slave端发送bin-log请求日志之后，将其指定的日志文件内容返回给slave端的IO线程。
- 从服务器（slave端）：slave端IO线程接受到返回来的日志，进行数据库操作，并记录当前所读取的位置，以便下一次master返回的最新sql操作。

实现配置：

环境准备：

主服务器：120.78.164.55 从服务器：47.254.47.72

主数据库版本：mysql Ver 14.14 Distrib 5.7.21 从数据库版本：mysql Ver 14.14 Distrib 5.6.40

数据库安装命令：yum install mysql mysql-devel mysql-server

数据库配置文件：/etc/my.cnf

主配置文件内容：

[mysqld] datadir = /data/mysql socket = /tmp/mysql.sock user = mysql log_bin = mysql-bin server-id = 1 ##通常只需加入这个，代表master服务器 port = 3306 [mysqld—safe] log_error = /data/mysql/mysql-error.log pid-file = /data/mysql/mysql.pid replicate-do-db = all ##通常只需加入这个，若有指定的数据库，可写入并用逗号隔开

[ mysqld ]

datadir = / data / mysql

socket = / tmp / mysql . sock

user = mysql

log_bin = mysql - bin

server - id = 1 ##通常只需加入这个，代表master服务器

port = 3306

[ mysqld— safe ]

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

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

replicate - do - db = all ##通常只需加入这个，若有指定的数据库，可写入并用逗号隔开

通过上面文件内容进行更改，并且查看是否有datadir的目录，若无则创建，若有则将其用户名和用户组进行mysql的更改。mkdir /data/mysql (创建目录) chown -R mysql:mysql /data/sql/(授权) service mysqld restart(重启数据库)

成功重启数据库后，进入datadir目录，我们可以看到相关的日志文件，其中mysql-bin.xxxxx为其日志文件。

从配置文件内容：

[mysqld] datadir = /data/mysql socket = /var/lib/mysql/mysql.sock user = mysql #skip-grant-tables # Disabling symbolic-links is recommended to prevent assorted security risks symbolic-links=0 log-bin=mysql-bin # Recommended in standard MySQL setup server-id=2 #比主服务器大，代表从服务器 #sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES [mysqld_safe] log-error=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid master-host = 120.78.164.55 #主服务数据库地址 master-user = denny #主服务数据库用户 master-pass = 8888888 #主服务数据库密码 master-port = 3306 #主服务器数据库端口 master-connect-retry = 60 #出错重置时间进行连接 replicate-do-db = all #指定数据库复制

[ mysqld ]

datadir = / data / mysql

socket = / var / lib / mysql / mysql . sock

user = mysql

#skip-grant-tables

# Disabling symbolic-links is recommended to prevent assorted security risks

symbolic - links = 0

log - bin = mysql - bin

# Recommended in standard MySQL setup

server - id = 2 #比主服务器大，代表从服务器

#sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

[ mysqld_safe ]

log - error = / var / log / mysqld . log

pid - file = / var / run / mysqld / mysqld . pid

master - host = 120.78.164.55 #主服务数据库地址

master - user = denny #主服务数据库用户

master - pass = 8888888 #主服务数据库密码

master - port = 3306 #主服务器数据库端口

master - connect - retry = 60 #出错重置时间进行连接

replicate - do - db = all #指定数据库复制

通过上面文件内容进行更改，我们可以通过该配置向主进行连接，请求日志。并且查看是否有datadir的目录，若无则创建，若有则将其用户名和用户组进行mysql的更改。mkdir /data/mysql (创建目录) chown -R mysql:mysql /data/sql/(授权) service mysqld restart(重启数据库)

成功重启数据库后，进入datadir目录，我们可以看到相关的日志文件，其中mysql-bin.xxxxx为其日志文件。

主配置从连接权限：

一般数据库不会通过远程连接，那么我们就可以通过命令创建一个用户并给予某些权限使其通过某个ip进行连接，命令如下：

grant replication slave on *.* to 'denny'@'%' identified by '888888' *.* 代表 (数据库).(数据表) *代表全部，严格可按照自己要求进行重写 'denny' (用户名) '%' (ip地址) %代表全部，更改为'X.X.X.X'，即指定ip可以访问 '888888' (密码)

grant replication slave on * . * to 'denny' @ '%' identified by '888888'

* . * 代表 (数据库 ) . (数据表 ) *代表全部，严格可按照自己要求进行重写

'denny' (用户名 )

'%' ( ip地址 ) %代表全部，更改为 'X.X.X.X'，即指定 ip可以访问

'888888' (密码 )

查看主日志文件名称以及所在的点：

mysql>show master status;

1	mysql > show master status ;

此处，我们可以知道当前数据库所在日志为mysql-bin.000010,所在的点为949900.查看作用是为了让从数据库从mster数据库获取mysql-bin.000010并且从点949900开始获取日志内容。

从配置指定的点:

mysql>change master to master_host='120.78.164.55',master_user='denney',master_password='888888',mast er_log_file='mysql-bin-000010',master_log_pos=949900;

1 2	mysql > change master to master_host = '120.78.164.55' , master_user = 'denney' , master_password = '888888' , mast er_log_file = 'mysql-bin-000010' , master_log_pos = 949900 ;

在从服务器指明master的IP以及同步日志的名称以及所在的点。这些内容获取由以上查看可以得知。

在slave启动

start slave;

1	start slave ;

查看主从状态。

show slave status\G

1	show slave status \ G

若Slave_IO_Running和Slave_SQL_Running都为yes，则主从配置成功！！

最后我们进行试验，在主服务器创建数据库，看看相应的从服务器是否也创立了数据库，若是则说明配置成功。

注意事项

一.有时候因为数据库被操作，所以所在的点会变化，而在操作命令时，记得把相关的master_log_pos修改为最新。

stop slave; change master to master_host='120.78.164.55',master_user='denney',master_password='888888',mast er_log_file='mysql-bin-000010',master_log_pos=949900; start slave;

stop slave ;

change master to master_host = '120.78.164.55' , master_user = 'denney' , master_password = '888888' , mast er_log_file = 'mysql-bin-000010' , master_log_pos = 949900 ;

start slave ;

二.有时候进行主从服务，可能会导致出错。比如主表有数据库而从从表没有创建该数据库，导致数据插入错误。这时候主从配置会不进行。那么我们进行排查解决后，可以进行命令对错误进行忽略。

set GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;