Linux(ubuntu)mysql数据库主从同步、配置

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
RDS MySQL Serverless 高可用系列,价值2615元额度,1个月
简介: 一、查看两台主机的版本Ubuntu 16.04.3root@iZ2zeht3zvxbq5ycy698pwZ:~# lsb_release -aLSB Version: core-9.

一、查看两台主机的版本Ubuntu 16.04.3

root@iZ2zeht3zvxbq5ycy698pwZ:~# lsb_release -a
LSB Version:    core-9.20160110ubuntu0.2-amd64:core-9.20160110ubuntu0.2-noarch:security-9.20160110ubuntu0.2-amd64:security-9.20160110ubuntu0.2-noarch
Distributor ID: Ubuntu
Description:    Ubuntu 16.04.3 LTS
Release:    16.04
Codename:   xenial
root@iZ2zeht3zvxbq5ycy698pwZ:~# 

二、查看mysql的版本

mysql> select version();
+-------------------------+
| version()               |
+-------------------------+
| 5.7.21-0ubuntu0.16.04.1 |
+-------------------------+
1 row in set (0.00 sec)

mysql> 

三、以39.107.227.105当主机,39.107.116.135当从机
先登录主机 A,在主服务器上,设置一个从数据库的账户,使用REPLICATION SLAVE(从复制)赋予权限,如:

mysql> GRANT REPLICATION SLAVE ON *.* TO 'backup'@'39.107.116.135' IDENTIFIED BY '123456';
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> 

刷新权限

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
mysql> 

如果有多台主机,就需要执行多次

四、(这个方法不要用,请看第五步)(坑,这是个坑,mysql5.7版本的改了my.cnf根本就无法使用)打开主机A的my.cnf,输入如下:(修改主数据库的配置文件my.cnf,开启BINLOG,并设置server-id的值,修改之后必须重启mysql服务),位于/etc/mysql下的my.cnf

server-id               = 1    #主机标示,整数
log_bin                 = /var/log/mysql/mysql-bin.log   #确保此文件可写,开启bin-log
read-only              =0  #主机,读写都可以
binlog-do-db         =test   #指定mysql的binlog日志记录哪个db,多个写多行
binlog-ignore-db    =mysql #不需要备份的数据库,多个写多行
注意:如果要让mysql监听到其他的地址,可以将bind-address = 127.0.0.1注释掉。 
或者将bind-address = 0.0.0.0监听所有的地址 
1. 如果不按照如上所述设置,会报出错误信息:MYSQL ERROR 2003 (HY000): Can’t connect to MySQL server on192.168.1.X’ (111) 
2. 如果按照如上所述设置,报出错误信息:Host ‘192.168.1.X’ is not allowed to connect to this MySQL server ,那么请检查数据库账号的权限是否有问题。

五、mysql5.7版本的请修改,/etc/mysql/mysql.conf.d,以下mysqld.cnf文件的配置

root@iZ2zeht3zvxbq5ycy698pwZ:/etc/mysql/mysql.conf.d# pwd
/etc/mysql/mysql.conf.d

使用vi编辑mysqld.cnf

# The following can be used as easy to replay backup logs or for replication.
# note: if you are setting up a replication slave, see README.Debian about
#       other settings you may need to change.
#
server-id               = 1
log_bin                 = /var/log/mysql/mysql-bin.log
expire_logs_days        = 10
max_binlog_size   = 100M
binlog_do_db            = test (这个地方是指定同步的数据库)
binlog_ignore_db        = mysql(这个地方指定不同步的数据库)

六、重启一下mysql

root@iZ2zeht3zvxbq5ycy698pwZ:~# service mysql restart

七、通过以下命令验证二进制日志是否已经启动

mysql> show variables like 'log_%';
+----------------------------------------+--------------------------------+
| Variable_name                          | Value                          |
+----------------------------------------+--------------------------------+
| log_bin                                | ON                             |
| log_bin_basename                       | /var/log/mysql/mysql-bin       |
| log_bin_index                          | /var/log/mysql/mysql-bin.index |
| log_bin_trust_function_creators        | OFF                            |
| log_bin_use_v1_row_events              | OFF                            |
| log_builtin_as_identified_by_password  | OFF                            |
| log_error                              | /var/log/mysql/error.log       |
| log_error_verbosity                    | 3                              |
| log_output                             | FILE                           |
| log_queries_not_using_indexes          | OFF                            |
| log_slave_updates                      | OFF                            |
| log_slow_admin_statements              | OFF                            |
| log_slow_slave_statements              | OFF                            |
| log_statements_unsafe_for_binlog       | ON                             |
| log_syslog                             | OFF                            |
| log_syslog_facility                    | daemon                         |
| log_syslog_include_pid                 | ON                             |
| log_syslog_tag                         |                                |
| log_throttle_queries_not_using_indexes | 0                              |
| log_timestamps                         | UTC                            |
| log_warnings                           | 2                              |
+----------------------------------------+--------------------------------+
21 rows in set (0.01 sec)

八、mysql从库配置(135的那台机),mysql5.7版本的请修改,/etc/mysql/mysql.conf.d,以下mysqld.cnf文件的配置
从库只需要开启server-id就行了
这里写图片描述
如果要设置多个从库,则每个从库的server-id与主库和其他从库设置不同的唯一值。

九、mysql重启一下

root@iZ2ze451u03knf2sumyavyZ:~# service mysql restart

十、配置主从库之间的通信
(105的那台机)查看 Master-Server , binlog File 文件名称和 Position值位置 并且记下来

mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 |     2021 | test         | mysql            |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

mysql> 

十一、要设置从库(135机)与主库(105机)进行通信,进行复制,使用必要的连接信息配置从库在从库上(135机)执行以下语句
将选项值替换为与系统相关的实际值
参数格式,请勿执行

mysql> CHANGE MASTER TO
    ->     MASTER_HOST='master_host_name',
    ->     MASTER_USER='replication_user_name',
    ->     MASTER_PASSWORD='replication_password',
    ->     MASTER_LOG_FILE='recorded_log_file_name',
    ->     MASTER_LOG_POS=recorded_log_position;

请将参数修改成符合你自己的,再执行

mysql> CHANGE MASTER TO
    -> MASTER_HOST='39.107.227.105',
    -> MASTER_USER='backup',
    -> MASTER_PASSWORD='123456',
    -> MASTER_LOG_FILE='mysql-bin.000001',
    -> MASTER_LOG_POS=2021;
Query OK, 0 rows affected, 2 warnings (0.03 sec)

mysql> 

下面这个参数对应于上图的Position

MASTER_LOG_POS=0 写成0 也是可以的

放在一行用逗号隔开,也是可以的

mysql> CHANGE MASTER TO MASTER_HOST='192.168.252.123', MASTER_USER='replication', MASTER_PASSWORD='mima', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=629;

十二、(135机)启动从数据库复制线程

mysql> START SLAVE;
Query OK, 0 rows affected (0.01 sec)

mysql> 

十三、(135机)查看复制状态

show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 39.107.227.105
                  Master_User: backup
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 2021
               Relay_Log_File: iZ2ze451u03knf2sumyavyZ-relay-bin.014660
                Relay_Log_Pos: 320
        Relay_Master_Log_File: mysql-bin.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
            ......

十四、(135)检查主从复制通信状态
这里写图片描述
这里写图片描述

Slave_IO_State #从站的当前状态 
Slave_IO_Running: Yes #读取主程序二进制日志的I/O线程是否正在运行 
Slave_SQL_Running: Yes #执行读取主服务器中二进制日志事件的SQL线程是否正在运行。与I/O线程一样 
Seconds_Behind_Master #是否为0,0就是已经同步了

如果不是:
主要有以下 4 个方面:
1、网络不通
2、密码不对
3、MASTER_LOG_POS 不对 ps
4、mysql 的 auto.cnf server-uuid 一样(可能你是复制的mysql)

第4问题可照一下操作

root@iZ2ze451u03knf2sumyavyZ:~# find / -name 'auto.cnf'
/var/lib/mysql/auto.cnf
quit
^C
root@iZ2ze451u03knf2sumyavyZ:~# vi /var/lib/mysql/auto.cnf
server-uuid=6b831bf3-8ae7-11e7-a178-000c29cb5cbc # 按照这个16进制格式,修改server-uuid,重启mysql service mysql restart ,并开启从库同步即可
root@iZ2ze451u03knf2sumyavyZ:~# service mysql restart
root@iZ2ze451u03knf2sumyavyZ:~# mysql -uroot -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.7.21-0ubuntu0.16.04.1 (Ubuntu)

Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> START SLAVE
    -> quit;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'quit' at line 2
mysql> START SLAVE;
Query OK, 0 rows affected, 1 warning (0.00 sec)

十五:通过第十步,查看到master是指定了同步的数据库和不同步的数据库。
那么我们现在不想指定这两个参数。
1.只需要回去将那两个变量给注释掉:

root@iZ2zeht3zvxbq5ycy698pwZ:/etc/mysql/mysql.conf.d# ls
mysqld.cnf  mysqld_safe_syslog.cnf
root@iZ2zeht3zvxbq5ycy698pwZ:/etc/mysql/mysql.conf.d# vi mysqld.cnf

2.重启主库(105机)

root@iZ2zeht3zvxbq5ycy698pwZ:/etc/mysql/mysql.conf.d# service mysql restart

3.登陆主库(105),查看主库状态

mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000002 |      154 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

4.登陆从库(135),从新配置跟随的主库信息
先停止线程

mysql> Stop SLAVE;
Query OK, 0 rows affected (0.01 sec)

在从库(135中)配置同步主库(105)

mysql> CHANGE MASTER TO MASTER_HOST='39.107.227.105', MASTER_USER='backup', MASTER_PASSWORD='123456', MASTER_LOG_FILE='mysql-bin.000002', MASTER_LOG_POS=154;
Query OK, 0 rows affected, 2 warnings (0.01 sec)

5.启动从库同步线程

mysql> START SLAVE;
Query OK, 0 rows affected (0.01 sec)

十五、测试主从同步情况
1.在主库中(105)在 Master-Server 创建测试库,使用创建的测试库,并创建表

mysql> CREATE DATABASE `replication_wwww.ymq.io`;
Query OK, 1 row affected (0.00 sec)
mysql> use `replication_wwww.ymq.io`;
Database changed
mysql> CREATE TABLE `sync_test` (`id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(255) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.02 sec)
mysql> 

2.查看从库(135机)Slave-Server 查看是否同步过来

mysql> show databases
    -> ;
+-------------------------+
| Database                |
+-------------------------+
| information_schema      |
| mysql                   |
| performance_schema      |
| replication_wwww.ymq.io |
| sys                     |
+-------------------------+
5 rows in set (0.00 sec)

mysql> 

同步完成。
后记:搞技术要的就是耐心和解决问题的能力,不要急,一步步慢慢来,祝您成功。

相关实践学习
基于CentOS快速搭建LAMP环境
本教程介绍如何搭建LAMP环境,其中LAMP分别代表Linux、Apache、MySQL和PHP。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
目录
相关文章
|
6天前
|
SQL Oracle 关系型数据库
探索 Linux 命令 `db_archive`:Oracle 数据库归档日志的工具
探索 Linux 中的 `db_archive`,实际与 Oracle 数据库归档日志管理相关。在 Oracle 中,归档日志用于恢复,当在线重做日志满时自动归档。管理员可使用 SQL*Plus 查看归档模式,通过 `RMAN` 进行备份和恢复操作。管理归档日志需谨慎,避免数据丢失。了解归档管理对 Oracle 管理员至关重要,确保故障时能快速恢复数据库。
|
6天前
|
数据可视化 Linux 数据库
探索 Linux 命令 `db_tuner`:优化数据库性能的利器
`db_tuner` 是一个假设的 Linux 工具,用于数据库性能优化,提供状态检查、查询分析、配置调优、索引建议及报告生成等功能。虽然不是真实命令,但类似工具如 `mysqltuner` 可实现这些功能。通过安装和运行 `db_tuner`,DBA 可以提升数据库性能,解决瓶颈问题。
|
6天前
|
Oracle 关系型数据库 MySQL
深入了解 Linux 命令 `db_stat`:数据库统计信息的获取
本文模拟了 Linux 环境下使用 `db_stat` 命令获取数据库统计信息的场景,实际上`db_stat`是特定数据库系统的自定义工具。文章通过示例展示了如何针对Oracle、PostgreSQL和MySQL使用各自内置命令收集统计信息,强调了权限、性能影响和数据实时性等因素,并指出这些信息对优化数据库管理至关重要。
|
6天前
|
存储 关系型数据库 Linux
探索 `db_printlog`:Linux 下的数据库日志打印工具
`db_printlog`是Linux下用于解析和打印Berkeley DB日志文件的命令行工具,帮助用户以可读格式理解数据库事务和系统事件。要使用它,先安装Berkeley DB,然后通过`db_printlog logfilename`命令分析日志。常见选项包括`-h`(显示帮助)、`-v`(详细输出)和`-f`(跟随日志更新)。注意日志文件权限和大小问题,此工具适用于Berkeley DB,其他数据库系统可能有不同解析工具。
|
6天前
|
存储 缓存 Linux
深入理解Linux中的`db_load`命令:数据库加载的利器
`db_load`是Linux下处理Berkeley DB的关键命令,用于将文本数据加载到数据库中。它支持多种文本格式,如键值对和CSV,并具有灵活的选项,如指定数据库类型、缓存大小、日志记录和错误处理。通过`-f`加载文本文件,`-s`设定数据库类型,`-l`设置缓存。本文详细介绍了`db_load`的使用方法和高级特性,并给出案例,如将CSV用户信息加载到Btree数据库。了解并善用`db_load`能提升数据处理效率和安全性。
|
9天前
|
Web App开发 Ubuntu 安全
Linux中的certutil命令:处理证书与证书数据库的实用工具
`certutil`是Linux下的命令行工具,用于处理X.509证书和证书数据库,常与NSS库配合,服务于Firefox等应用。安装`certutil`可通过`apt-get install libnss3-tools`(Debian/Ubuntu)或`yum/dnf install nss-tools`(RHEL/Fedora/CentOS)。基本操作包括:使用`-L`列出证书数据库中的证书,`-A`添加证书,`-D`删除证书,`-x`导出证书。此外,还能用`-M`修改信任设置,`-C`列出证书链,`-V`验证证书链的有效性。了解这些功能有助于高效管理证书。
|
9天前
|
Ubuntu Java Linux
Ubuntu20.04下载jdk遇到各种奇怪问题大集,Linux如何将默认的OpenJDK切换成自己安装的JDK版本(以JDK1.8为例),无需卸载原有OpenJDK,Some packages co
Ubuntu20.04下载jdk遇到各种奇怪问题大集,Linux如何将默认的OpenJDK切换成自己安装的JDK版本(以JDK1.8为例),无需卸载原有OpenJDK,Some packages co
|
10天前
|
Ubuntu Linux Docker
windows 安装docker 安装Ubuntu linux
windows 安装docker 安装Ubuntu linux
|
10天前
|
Ubuntu Java Linux
Linux centos7 ubuntu 一键安装Java JDK 脚本 shell 脚本
Linux centos7 ubuntu 一键安装Java JDK 脚本 shell 脚本
|
10天前
|
Ubuntu 应用服务中间件 Linux
Linux Centos7 ubuntu 安装nginx,脚本一键安装nginx
Linux Centos7 ubuntu 安装nginx,脚本一键安装nginx