Linux上MySQL的主从配置

发布时间：2022-09-23 15:48:58 所属栏目：MySql教程来源：

导读：　　主从数据库原理

　　主库 master

　　当 master 每做一次 write 操作mysql主机，就会将操作的命令记录到 master 的 Binary-log（bin-log）日志中。

　　从库 slave

　　slave 中默认会有两条线程

　　主从数据库原理

　　主库 master

　　当 master 每做一次 write 操作mysql主机，就会将操作的命令记录到 master 的 Binary-log（bin-log）日志中。

　　从库 slave

　　slave 中默认会有两条线程

　　IO Thread：读取 master 中的 bin-log 内容，写入到 slave 中的 relay-log。

　　SQL Thread：slave 读取 relay-log，将操作写入到 slave 数据库。

　　一、云服务器配置

　　1.1 腾讯云

　　（1）在腾讯云控制台上，设置主数据库外网访问权限

　　（2）修改 MySQL配置文件，设置 3306 端口外部访问权限

　　sudo vi /etc/mysql/mysql.conf.d/mysqld.cnf

　　将 bind_address 的值由 '127.0.0.1'改成你腾讯云内网IP地址。

　　重启 MySQL 服务器，然后输入 netstat -anpt|grep 3306 检查3306端口是否显示腾讯云的内网IP。

　　1.2 阿里云

　　（1）修改主机 MySQL 配置文件：

　　vi /etc/mysql/mysql.cnf.d/mysqld.cnf

　　将 bind_address 一行注释掉。

　　（2）然后在阿里云 ECS 控制台上，添加网络安全组规则：

　　入方向

　　协议类型选择 MySQL(3306)

　　授权类型改为地址段访问

　　授权对象输入：0.0.0.0/0

　　二、MySQL 主机 master 配置

　　2.0 创建主机 MySQL 数据库

　　创建主机数据库：mytest

　　create database mytest default character set utf8;

　　2.1 修改 MySQL 配置文件

　　sudo vi /etc/mysql/mysql.conf.d/mysqld.cnf

　　在 [mysqld] 模块下添加如下内容（默认是注释的，解除注释即可）：

　　server-id = 1

　　log_bin = /var/log/mysql/mysql-bin.log

　　binlog_do_db = mytest # 需要做主从分离，主服务器 MySQL 的数据库名

　　binlog_ignore_db = mysql # 在主服务器上需要忽略的 MySQL 数据库，避免从服务器对主服务器的安全性影响

　　数据表名忽略大小写，在 [mysqld] 模块下添加如下内容：

　　lower_case_table_names=1

　　2.2 创建一个 MySQL 数据库用户，提供给从机 slave 访问

　　CREATE USER 'username'@'host' IDENTIFIED BY 'password';

　　说明:

　　? username - 你将创建的用户名

　　? host - 指定该用户在哪个主机上可以登陆。如果是本地用户可用 localhost，如果想让该用户可以从任意远程主机登陆，可以使用通配符 % 。

　　? password - 该用户的登陆密码，密码可以为空，如果为空则该用户可以不需要密码登陆服务器.。

　　举例：

　　CREATE USER 'mytest'@'%' IDENTIFIED BY 'mytest123!@#';

　　2.3 给刚才创建的用户授权

　　grant all privileges on *.* to 'mytest'@'%'identified by 'mytest123!@#' with grant option;

　　flush privileges; -- 更新权限，使之生效

　　? all privileges 表示授权全部的权限。如果单独指定权限，可以替换成：insert、select、update、delete 等等；

　　? *.* 表示这些权限是对所有的数据库.表等生效。如果要单独指定，可以按照此格式 databasename.tablename ；

　　? with grant option 表示创建的这个用户还可以为其他用户继续赋予这些权限。

　　2.4 赋予从机复制主机数据的权力

　　从机可以通过主机创建的这个用户连接上主机数据库，然后复制主机的数据到从机数据库。

　　GRANT REPLICATION SLAVE ON *.* TO 'mytest'@'%' IDENTIFIED BY 'mytest123!@#';

　　2.5 重启主机 MySQL 服务

　　sudo /etc/init.d/mysql restart

　　2.6 登录主机 MySQL，检查主机 master 的状态：

　　show master status;

　　会显示如下内容（例子）：

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

　　| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |

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

　　| mysql-bin.000001 | 154 | | |

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

　　1 row in set (0.00 sec)

　　注意记录 File 和 Position 的值，在从机 slave 配置时会用到该属性。

　　注：执行完此步骤后不要再操作主服务器 MySQL，防止主服务器 master 状态值变化。如果对主机数据库做了增删改操作，会导致 Position 值发生变化。

　　三、MySQL 从机 slave 配置

　　3.1 修改从机 MySQL 配置文件

　　sudo vi /etc/mysql/mysql.conf.d/mysqld.cnf

　　在 [mysqld] 模块中添加如下内容：

　　server-id = 2

　　relay-log = slave-relay-bin

　　relay-log-index = slave-relay-bin.index

　　主机中是 server-id = 1，而从机与主机不一样，注意区分。

　　3.2 重启从机 MySQL 服务

　　sudo /etc/init.d/mysql restart

　　3.3 登录从机 MySQL，配置与主服务器 master 的连接

　　使用 root 帐号登录到从机 MySQL，执行以下命令，将从机与主机关联：

　　-- 例子

　　change master to master_host='120.77.219.39',master_port=3306,master_user='mytest',master_password='mytest123!@#',master_log_file='mysql-bin.000001',master_log_pos=154;

　　说明：

　　? master_host 对应主服务器的外网IP地址 120.77.219.39

　　? master_port 对应主服务器的端口（3306）

　　? master_log_file 对应 show master status 显示的 File 列：mysql-bin.000001

　　? master_log_pos 对应前面记录主机中显示的 Position 的值，否则有可能出现同步失败。

　　3.4 在从服务器上创建同名从数据库，同名用户

　　该数据库名称需要与主服务器上 MySQL 配置文件中定义的 binlog_do_db = mytest 名称一致：

　　create database mytest default character set utf8;

　　创建与主数据库同名的用户（非必需）：

　　CREATE USER 'mytest'@'%' IDENTIFIED BY 'mytest123!@#';

　　grant all privileges on *.* to 'mytest'@'%'identified by 'mytest123!@#' with grant option;

　　flush privileges; -- 更新权限，使之生效

　　注意：“创建同名用户” 这一步是非必需的，但为了方便读写，建议对 master 或 slave 数据库访问采用同样的一套用户名密码。

　　3.5 重启从服务器（slave）上的 MySQL 服务

　　/etc/init.d/mysql restart

　　3.6 登录从机 MySQL，启用 slave 数据同步

　　登录 MySQL 后，执行如下命令：

　　start slave;

　　如果出现错误：

　　mysql> start slave;

　　ERROR 1872 (HY000): Slave failed to initialize relay log info structure from the repository

　　说明 relay log 即 mysql.slave_relay_log_info 表中保留了以前的主从复制信息，导致新从库启动时无法找到对应文件，那么我们只需要清理掉该表中的记录就可以了。注意，不要手动删该表数据，MySQL 已经提供了解决放哪广发，输入命令：

　　reset slave;

　　执行成功后，再来执行一遍启动 slave 同步：

　　start slave;

　　补充几个个与 slave 相关的命令：

　　1. 停止当前在运行的 slave

　　stop slave;

　　2. 显示当前 slave 的状态

　　show slave status;

（编辑：我爱制作网_沈阳站长网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!

什么是MySQL 连接查询	完全卸载SQLServer 20
MySQL NULLIF函数用处	MySQL的主从复制简介及