mysql主从
环境说明：
| 数据库角色 | IP | 应用与系统版本 | 有无数据 |
|---|---|---|---|
| 主数据库 | 192.168.100.240 | Rocky Linux release 9.3 mysql8.0.35 | 无数据 |
| 从数据库 | 192.168.100.230 | Rocky Linux release 9.3 mysql8.0.35 | 无数据 |
前期准备
所有主机都关闭防火墙和selinux
mysql安装
master
传数据库包
[root@master ~]# ls
anaconda-ks.cfg Desktop Documents Downloads Music mysql-8.0.35-linux-glibc2.28-x86_64.tar.xz Pictures Public Templates Videos
[root@master ~]# scp mysql-8.0.35-linux-glibc2.28-x86_64.tar.xz 192.168.100.230:/root/
The authenticity of host '192.168.100.230 (192.168.100.230)' can't be established.
ED25519 key fingerprint is SHA256:YAjG0yoPfihopvhl1clD3AgVfaGEcICGiNumwnugD0w.
This key is not known by any other names
Are you sure you want to continue connecting (yes/no/[fingerprint])? y
Please type 'yes', 'no' or the fingerprint: yes
Warning: Permanently added '192.168.100.230' (ED25519) to the list of known hosts.
root@192.168.100.230's password:
mysql-8.0.35-linux-glibc2.28-x86_64.tar.xz 100% 438MB 140.9MB/s 00:03
解压改名
[root@master ~]# tar xf mysql-8.0.35-linux-glibc2.28-x86_64.tar.xz -C /usr/local/
[root@master ~]# cd /usr/local/
[root@master local]# ls
bin etc games include lib lib64 libexec mysql-8.0.35-linux-glibc2.28-x86_64 sbin share src
[root@master local]# mv mysql-8.0.35-linux-glibc2.28-x86_64 mysql
[root@master local]# ls
bin etc games include lib lib64 libexec mysql sbin share src
[root@master local]#
创建用户改属组
[root@master local]# useradd -r -M -s /sbin/nologin mysql
[root@master local]# chown -R mysql.mysql /usr/local/mysql
[root@master local]# ll
total 0
drwxr-xr-x. 2 root root 6 May 16 2022 bin
drwxr-xr-x. 2 root root 6 May 16 2022 etc
drwxr-xr-x. 2 root root 6 May 16 2022 games
drwxr-xr-x. 2 root root 6 May 16 2022 include
drwxr-xr-x. 2 root root 6 May 16 2022 lib
drwxr-xr-x. 3 root root 17 Nov 29 09:13 lib64
drwxr-xr-x. 2 root root 6 May 16 2022 libexec
drwxr-xr-x. 9 mysql mysql 129 Dec 26 03:49 mysql
drwxr-xr-x. 2 root root 6 May 16 2022 sbin
drwxr-xr-x. 5 root root 49 Nov 29 09:13 share
drwxr-xr-x. 2 root root 6 May 16 2022 src
[root@master local]#
配置
[root@master local]# ls /usr/local/mysql/
bin docs include lib LICENSE man README share support-files
[root@master local]# echo 'export PATH=/usr/local/mysql/bin:$PATH' > /etc/profile.d/mysql.sh
[root@master local]# source /etc/profile.d/mysql.sh
[root@master local]# ln -s /usr/local/mysql/include/ /usr/include/mysql
[root@master local]# vim /etc/ld.so.conf.d/mysql.conf
[root@master local]# cat /etc/ld.so.conf.d/mysql.conf
/usr/local/mysql/lib
[root@master local]#
[root@master local]# ldconfig -v
/usr/local/mysql/lib: (from /etc/ld.so.conf.d/mysql.conf:1)
[root@master local]# ls /usr/local/mysql/support-files/
mysqld_multi.server mysql-log-rotate mysql.server
[root@master local]# vim /usr/local/mysql/support-files/mysql.server
basedir=/usr/local/mysql
datadir=/opt/data
[root@master local]# vim /etc/my.cnf
[root@master local]# cat /etc/my.cnf
[mysqld]
basedir=/usr/local/mysql
datadir=/opt/data
pid-file=/opt/data/mysql.pid
port=3306
skip-name-resolve
user=mysql
socket = /tmp/mysql.sock
[root@master local]# mkdir -p /opt/data
[root@master local]# chown -R mysql.mysql /opt/data
[root@master local]# ll /opt/
total 0
drwxr-xr-x. 2 mysql mysql 6 Dec 26 04:16 data
[root@master local]#
初始化
[root@master local]# mysqld --initialize --user mysql --datadir /opt/data
2023-12-26T09:25:32.805842Z 0 [System] [MY-013169] [Server] /usr/local/mysql/bin/mysqld (mysqld 8.0.35) initializing of server in progress as process 3459
2023-12-26T09:25:32.813604Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2023-12-26T09:25:33.539453Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2023-12-26T09:25:35.099275Z 6 [Note] [MY-010454] [Server] A temporary password is generated for root@localhost: >)pypy.%Q5_N
[root@master local]# echo '>)pypy.%Q5_N' > pass
[root@master local]#
[root@master ~]# cp /usr/lib/systemd/system/sshd.service /usr/lib/systemd/system/mysqld.service
[root@master ~]# vim /usr/lib/systemd/system/mysqld.service
[root@master ~]# cat /usr/lib/systemd/system/mysqld.service
[Unit]
Description=mysqld server daemon
After=network.target
[Service]
Type=forking
ExecStart=/usr/local/mysql/support-files/mysql.server start
ExecStop=/usr/local/mysql/support-files/mysql.server stop
ExecReload=/bin/kill -HUP $MAINPID
[Install]
WantedBy=multi-user.target
[root@master ~]# scp /usr/lib/systemd/system/mysqld.service 192.168.100.230:/usr/lib/systemd/system/
root@192.168.100.230's password:
mysqld.service 100% 277 266.6KB/s 00:00
[root@master ~]#
//启动
[root@master ~]# systemctl daemon-reload
[root@master ~]# systemctl enable --now mysqld
Created symlink /etc/systemd/system/multi-user.target.wants/mysqld.service → /usr/lib/systemd/system/mysqld.service.
[root@master ~]# systemctl status mysqld
● mysqld.service - mysqld server daemon
Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; preset: disabled)
Active: active (running) since Tue 2023-12-26 04:40:51 EST; 36s ago
Process: 3817 ExecStart=/usr/local/mysql/support-files/mysql.server start (code=exited, status=0/SUCCESS)
Main PID: 3830 (mysqld_safe)
Tasks: 39 (limit: 22744)
Memory: 374.5M
CPU: 1.039s
CGroup: /system.slice/mysqld.service
├─3830 /bin/sh /usr/local/mysql/bin/mysqld_safe --datadir=/opt/data --pid-file=/opt/data/mysql.pid
└─4018 /usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql --datadir=/opt/data --plugin-dir=/usr/local/mysql/lib/plugin>
Dec 26 04:40:50 master systemd[1]: Starting mysqld server daemon...
Dec 26 04:40:50 master mysql.server[3817]: Starting MySQL.
Dec 26 04:40:50 master mysql.server[3830]: Logging to '/opt/data/master.err'.
Dec 26 04:40:51 master mysql.server[3817]: SUCCESS!
Dec 26 04:40:51 master systemd[1]: Started mysqld server daemon.
[root@master ~]# ss -antl
State Recv-Q Send-Q Local Address:Port Peer Address:Port Process
LISTEN 0 4096 127.0.0.1:631 0.0.0.0:*
LISTEN 0 128 0.0.0.0:22 0.0.0.0:*
LISTEN 0 128 127.0.0.1:6010 0.0.0.0:*
LISTEN 0 151 *:3306 *:*
LISTEN 0 4096 [::1]:631 [::]:*
LISTEN 0 70 *:33060 *:*
LISTEN 0 128 [::]:22 [::]:*
LISTEN 0 128 [::1]:6010 [::]:*
[root@master ~]#
改数据库密码
[root@master ~]# cat /usr/local/pass
>)pypy.%Q5_N
[root@master ~]# mysql -uroot -p'>)pypy.%Q5_N'
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.35
Copyright (c) 2000, 2023, 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.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> alter user root@'localhost' identified with mysql_native_password by '123456789';
Query OK, 0 rows affected (0.00 sec)
mysql>
slave
解压改名
[root@slave ~]# tar xf mysql-8.0.35-linux-glibc2.28-x86_64.tar.xz -C /usr/local/
[root@slave ~]# cd /usr/local/
[root@slave local]# ls
bin etc games include lib lib64 libexec mysql-8.0.35-linux-glibc2.28-x86_64 sbin share src
[root@slave local]# mv mysql-8.0.35-linux-glibc2.28-x86_64 mysql
[root@slave local]# ls
bin etc games include lib lib64 libexec mysql sbin share src
[root@slave local]#
创建用户改属组
[root@slave local]# useradd -r -M -s /sbin/nologin mysql
[root@slave local]# chown -R mysql.mysql /usr/local/mysql
[root@slave local]# ll
total 0
drwxr-xr-x. 2 root root 6 May 16 2022 bin
drwxr-xr-x. 2 root root 6 May 16 2022 etc
drwxr-xr-x. 2 root root 6 May 16 2022 games
drwxr-xr-x. 2 root root 6 May 16 2022 include
drwxr-xr-x. 2 root root 6 May 16 2022 lib
drwxr-xr-x. 3 root root 17 Nov 29 09:13 lib64
drwxr-xr-x. 2 root root 6 May 16 2022 libexec
drwxr-xr-x. 9 mysql mysql 129 Dec 26 03:50 mysql
drwxr-xr-x. 2 root root 6 May 16 2022 sbin
drwxr-xr-x. 5 root root 49 Nov 29 09:13 share
drwxr-xr-x. 2 root root 6 May 16 2022 src
[root@slave local]#
配置
[root@slave local]# ls
bin etc games include lib lib64 libexec mysql sbin share src
[root@slave local]# echo 'export PATH=/usr/local/mysql/bin:$PATH' > /etc/profile.d/mysql.sh
[root@slave local]# source /etc/profile.d/mysql.sh
[root@slave local]# ln -s /usr/local/mysql/include/ /usr/include/mysql
[root@slave local]# vim /etc/ld.so.conf.d/mysql.conf
[root@slave local]# cat /etc/ld.so.conf.d/mysql.conf
/usr/local/mysql/lib
[root@slave local]#
[root@slave local]# ldconfig -v
/usr/local/mysql/lib: (from /etc/ld.so.conf.d/mysql.conf:1)
[root@slave local]# ls /usr/local/mysql/support-files/
mysqld_multi.server mysql-log-rotate mysql.server
[root@slave local]# vim /usr/local/mysql/support-files/mysql.server
basedir=/usr/local/mysql
datadir=/opt/data
[root@slave local]# vim /etc/my.cnf
[root@slave local]# cat /etc/my.cnf
[mysqld]
basedir=/usr/local/mysql
datadir=/opt/data
pid-file=/opt/data/mysql.pid
port=3306
skip-name-resolve
user=mysql
socket = /tmp/mysql.sock
[root@slave local]# mkdir -p /opt/data
[root@slave local]# chown -R mysql.mysql /opt/data
[root@slave local]# ll /opt/
total 0
drwxr-xr-x. 2 mysql mysql 6 Dec 26 04:21 data
[root@slave local]#
初始化
[root@slave local]# mysqld --initialize --user mysql --datadir /opt/data
2023-12-26T09:24:29.962014Z 0 [System] [MY-013169] [Server] /usr/local/mysql/bin/mysqld (mysqld 8.0.35) initializing of server in progress as process 2555
2023-12-26T09:24:29.971298Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2023-12-26T09:24:30.943951Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2023-12-26T09:24:33.180714Z 6 [Note] [MY-010454] [Server] A temporary password is generated for root@localhost: XVgtuN*j3qjk
[root@slave local]# echo 'XVgtuN*j3qjk' > ~/pass
//master传过来的
[root@slave ~]# cat /usr/lib/systemd/system/mysqld.service
[Unit]
Description=mysqld server daemon
After=network.target
[Service]
Type=forking
ExecStart=/usr/local/mysql/support-files/mysql.server start
ExecStop=/usr/local/mysql/support-files/mysql.server stop
ExecReload=/bin/kill -HUP $MAINPID
[Install]
WantedBy=multi-user.target
[root@slave ~]#
//启动
[root@slave ~]# systemctl daemon-reload
[root@slave ~]# systemctl enable --now mysqld
Created symlink /etc/systemd/system/multi-user.target.wants/mysqld.service → /usr/lib/systemd/system/mysqld.service.
[root@slave ~]# systemctl status mysqld
● mysqld.service - mysqld server daemon
Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; preset: disabled)
Active: active (running) since Tue 2023-12-26 04:41:53 EST; 6s ago
Process: 2759 ExecStart=/usr/local/mysql/support-files/mysql.server start (code=exited, status=0/SUCCESS)
Main PID: 2772 (mysqld_safe)
Tasks: 39 (limit: 22744)
Memory: 373.9M
CPU: 2.529s
CGroup: /system.slice/mysqld.service
├─2772 /bin/sh /usr/local/mysql/bin/mysqld_safe --datadir=/opt/data --pid-file=/opt/data/mysql.pid
└─2960 /usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql --datadir=/opt/data --plugin-dir=/usr/local/mysql/lib/plugin>
Dec 26 04:41:50 slave systemd[1]: Starting mysqld server daemon...
Dec 26 04:41:51 slave mysql.server[2759]: Starting MySQL.
Dec 26 04:41:51 slave mysql.server[2772]: Logging to '/opt/data/slave.err'.
Dec 26 04:41:53 slave mysql.server[2759]: .. SUCCESS!
Dec 26 04:41:53 slave systemd[1]: Started mysqld server daemon.
[root@slave ~]# ss -antl
State Recv-Q Send-Q Local Address:Port Peer Address:Port Process
LISTEN 0 128 127.0.0.1:6011 0.0.0.0:*
LISTEN 0 4096 127.0.0.1:631 0.0.0.0:*
LISTEN 0 128 0.0.0.0:22 0.0.0.0:*
LISTEN 0 151 *:3306 *:*
LISTEN 0 128 [::1]:6011 [::]:*
LISTEN 0 4096 [::1]:631 [::]:*
LISTEN 0 70 *:33060 *:*
LISTEN 0 128 [::]:22 [::]:*
[root@slave ~]#
改数据库密码
[root@slave ~]# cat pass
XVgtuN*j3qjk
[root@slave ~]# mysql -uroot -p'XVgtuN*j3qjk'
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.35
Copyright (c) 2000, 2023, 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.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> alter user root@'localhost' identified with mysql_native_password by '123456789';
Query OK, 0 rows affected (0.00 sec)
mysql>
主从配置
确保主从数据库一直
主
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.00 sec)
mysql>
从
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.00 sec)
mysql>
在主数据库里创建一个同步账号授权给从数据库使用
mysql> create user repl@192.168.100.230 identified with mysql_native_password by'123456';
Query OK, 0 rows affected (0.01 sec)
mysql> grant replication slave on *.* to repl@192.168.100.230;
Query OK, 0 rows affected (0.01 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
mysql>
//从可以连上主
[root@slave ~]# mysql -urepl -p'123456' -h192.168.100.240
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 10
Server version: 8.0.35 MySQL Community Server - GPL
Copyright (c) 2000, 2023, 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.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>
配置主数据库
[root@master ~]# vim /etc/my.cnf
[root@master ~]# cat /etc/my.cnf
[mysqld]
basedir=/usr/local/mysql
datadir=/opt/data
pid-file=/opt/data/mysql.pid
port=3306
skip-name-resolve
user=mysql
socket = /tmp/mysql.sock
log-bin=mysql_bin
server-id = 10
default_authentication_plugin=mysql_native_password //这行不一定需要具体看下面解释
//show master status 这个命令里面的File会显示是 mysql_bin.就不需要加上
如果是binlog.才需要加上一行（本人水平有限，不一定正确）
//后面配置并启动主从之后使用show slave status\G
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
如果都是yes就是正确的
如果有下面这个报错
Last_IO_Error: Error connecting to source repl@192.168.100.240:3306 . This was attempt 1/86400, with a delay of 60 seconds between attempts. Message: Authentication plugin caching_sh
可以加上default_authentication_plugin=mysql_native_password这行
加上需要重启mysql （水平有限，不一定正确）
[root@master ~]#
[root@master ~]# systemctl restart mysqld.service
//查看主库状态
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql_bin.000004 | 157 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
mysql>
配置从数据库
[root@slave ~]# vi /etc/my.cnf
[root@slave ~]# cat /etc/my.cnf
[mysqld]
basedir=/usr/local/mysql
datadir=/opt/data
pid-file=/opt/data/mysql.pid
port=3306
skip-name-resolve
user=mysql
socket = /tmp/mysql.sock
server-id = 20
relay-log = my_relay
default_authentication_plugin=mysql_native_password
[root@slave ~]#
[root@slave ~]# systemctl restart mysqld
//配置并启动主从复制
mysql> change master to
-> master_host='192.168.100.240',
-> master_user='repl',
-> master_port=3306,
-> master_password='123456',
-> master_log_file='mysql_bin.000004',
-> master_log_pos=157;
Query OK, 0 rows affected, 9 warnings (0.02 sec)
mysql>
mysql> start slave;
Query OK, 0 rows affected, 1 warning (0.02 sec)
mysql>
mysql> satrt slave;
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 'satrt slave' at line 1
mysql> start slave;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for source to send event
Master_Host: 192.168.100.240
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql_bin.000004
Read_Master_Log_Pos: 157
Relay_Log_File: my_relay.000002
Relay_Log_Pos: 326
Relay_Master_Log_File: mysql_bin.000004
Slave_IO_Running: Yes //这里
Slave_SQL_Running: Yes //这里
Replicate_Do_DB:
Replicate_Ignore_DB:
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: 157
Relay_Log_Space: 529
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: 10
Master_UUID: b7d9e731-a3d0-11ee-9d4f-000c2955ab78
Master_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Replica 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:
Master_public_key_path:
Get_master_public_key: 0
Network_Namespace:
1 row in set, 1 warning (0.00 sec)
mysql>
测试
主数据库
mysql> create database ysy;
Query OK, 1 row affected (0.01 sec)
mysql> use ysy;
Database changed
mysql> create table test(id int(4) not null , name varchar(10) );
Query OK, 0 rows affected, 1 warning (0.01 sec)
mysql> insert test values(1,'ysy'),(2,'my');
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql>
从数据库
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| ysy |
+--------------------+
5 rows in set (0.00 sec)
mysql> use ysy;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> show tables;
+---------------+
| Tables_in_ysy |
+---------------+
| test |
+---------------+
1 row in set (0.00 sec)
mysql> select * from test;
+----+------+
| id | name |
+----+------+
| 1 | ysy |
| 2 | my |
+----+------+
2 rows in set (0.00 sec)
mysql>
2060
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
