mysql主从复制

最新推荐文章于 2024-07-11 20:24:07 发布

小虾战天

最新推荐文章于 2024-07-11 20:24:07 发布

阅读量403

点赞数 8

本文链接：https://blog.csdn.net/qq_36784778/article/details/136768491

版权

前言

本文旨在记录我学习的过程，让自己更好的理解学习到的内容。如果有不对的地方，欢迎指证。

文中部分内容参考GPT，在此感谢ppword提供的GPT工具的支持，有了它就再也不用去找大神帮忙了。

Mysql的安装及配置(ubuntu)

1、安装Mysql

1.1、通过命令安装

1.1.1、通过命令无法安装指定的版本

如：5.7，下载5.7的源，源的地址：https://repo.mysql.com/

wget https://repo.mysql.com//mysql-apt-config_0.8.12-1_all.deb
sudo dpkg -i mysql-apt-config_0.8.10-1_all.deb

之后，运行apt update更新源，这时候会在/etc/apt/source.list.d/目录下生成mysql.list
更新完成，我们通过apt-cache policy mysql-server可以查看到，系统中出现了mysql5.7的源
这个时候，我们就可以通过apt install mysql-xxx=5.7.31-1ubuntu18.04的方式安装mysql5.7版本了

apt install mysql-client=5.7.30-1ubuntu18.04
apt install mysql-community-server=5.7.30-1ubuntu18.04
apt install mysql-server=5.7.30-1ubuntu18.04

1.1.2、通过命令安装默认版本

apt install mysql-server

1.2、下载安装包安装

通过dpkg -i mysql-*.deb的方式安装mysql5.7

wget https://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-server_5.7.31-1ubuntu18.04_amd64.deb-bundle.tar
tar -xvf mysql-server_5.7.31-1ubuntu18.04_amd64.deb-bundle.tar

解压出来都是deb文件，是我们不希望安装测试相关的包，所以删除了两个带test名称的deb文件，这样可以快速安装

rm -f mysql-testsuite_5.7.31-1ubuntu18.04_amd64.deb
rm -f mysql-community-test_5.7.30-1ubuntu18.04_amd64.deb
dpkg -i mysql-*.deb

2、配置mysql

2.1、初始化配置

mysql_secure_installation
 
#1
VALIDATE PASSWORD PLUGIN can be used to test passwords
and improve security. It checks the strength of password
and allows the users to set only those passwords which are
secure enough. Would you like to setup VALIDATE PASSWORD plugin?
Press y|Y for Yes, any other key for No: N (你想设置验证密码插件吗?选择N)
 
#2 设置密码
Please set the password for root here...
New password: (输入密码)
Re-enter new password: (重复输入)
 
#3 删除匿名账户
By default, a MySQL installation has an anonymous user,
allowing anyone to log into MySQL without having to have
a user account created for them...
Remove anonymous users? (Press y|Y for Yes, any other key for No) : N (我的选项)
 
#4 禁止root管理员从远程登录，这里我没有禁止
Normally, root should only be allowed to connect from
'localhost'. This ensures that someone cannot guess at
the root password from the network...
Disallow root login remotely? (Press y|Y for Yes, any other key for No) : N (我的选项)
 
#5 删除test数据库并取消对它的访问权限
By default, MySQL comes with a database named 'test' that
anyone can access...
Remove test database and access to it? (Press y|Y for Yes, any other key for No) : N (我的选项)
 
#6 刷新授权表，让初始化后的设定立即生效
Reloading the privilege tables will ensure that all changes
made so far will take effect immediately.
Reload privilege tables now? (Press y|Y for Yes, any other key for No) : Y (我的选项)

2.2、检查mysql服务状态

#ubuntu下systemctl 命令
systemctl status mysql.service 
 
 
#查看内容如下
root@18f4411cbf2f:/# service mysql status
 * /usr/bin/mysqladmin  Ver 8.42 Distrib 5.7.36, for Linux on x86_64
Copyright (c) 2000, 2021, Oracle and/or its affiliates.
 
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
 
Server version          5.7.36-0ubuntu0.18.04.1
Protocol version        10
Connection              Localhost via UNIX socket
UNIX socket             /var/run/mysqld/mysqld.sock
Uptime:                 4 sec
 
Threads: 1  Questions: 8  Slow queries: 0  Opens: 105  Flush tables: 1  Open tables: 98  Queries per second avg: 2.000

2.3、配置远程访问

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

找到address=127.0.0.1这一段文本，将它注释掉或则将它改成address = 0.0.0.0
保存退出，然后进入mysql数据库，执行授权命令：

mysql -u root -p
mysql> grant all on *.* to root@'192.168.0.%' identified by '你的密码' with grant option;
mysql> flush privileges;    # 刷新权限
mysql> exit

然后执行exit命令退出mysql服务，再执行如下命令重启mysql：

systemctl restart mysql.service

3、主从配置

3.1 配置master

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

[mysqld]
#找到并去掉注释
server-id=1
#binary log
log-bin=/var/log/mysql/mysql-bin.log
log-error=/var/log/mysql/error.log
#指定需要同步哪些数据库（默认全部数据库）
#binlog_do_db=xxxx
#指定需要 不同步哪些数据库（排除）
binlog_ignore_db=mysql

3.2 配置slave

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

[mysqld]
server-id=2
#binary log
log-bin=/var/log/mysql/mysql-bin.log

3.3 配置主从关系

主数据库
mysql -u root -p
# 创建并授权用户
mysql> grant replication slave,reload,super on *.* to 'root'@'192.168.0.%' identified by '你的密码';
 
Query OK, 0 rows affected (0.00 sec) 
mysql> show master status;      # 只有打开二进制日志，这句命令才有结果，表示当前数据库的二进制日志写到什么位置
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 |      462 |              | mysql            |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
# File：二进制文件名  Position：正在写入的位置

从数据库
mysql -u root -p
# 指定master (slave指定master作为自己的主库)
mysql> change master to master_host='主IP', master_user='root', master_password='你的密码', master_port=3306,master_log_file='mysql-bin.000001', master_log_pos=462;
# 开启主从同步
mysql> start slave;
# 查看状态
mysql> show slave status \G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.0.110
                  Master_User: root
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 462
               Relay_Log_File: ubuntu-relay-bin.000002
                Relay_Log_Pos: 320
        Relay_Master_Log_File: mysql-bin.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

必须保证以下两个为yes：
    Slave_IO_Running: 
    Slave_SQL_Running:

3.4、遇到的问题

3.4.1、MySQL同步故障：" Slave_SQL_Running:No"

stop slave;

change master to master_host='192.168.1.110', master_user='root', master_password='root', master_port=3306,master_log_file='mysql-bin.000008', master_log_pos=727;

start slave