devbox
菜鸟追梦旅行
这个屌丝很懒，什么也没留下！
关注作者
热门标签
热门文章
当前位置:   article > 正文

主从版本升级_主从_8.0.32_软链接_基于二进制日志文件

作者：菜鸟追梦旅行 | 2024-02-17 06:04:26

赞

踩

主从版本升级_主从_8.0.32_软链接_基于二进制日志文件

升级需求

将一套MySQL 8.0.32主从环境版本升级到8.0.35版本，主从环境如下：

主服务器

登录账号密码

端口

当前版本

目标版本

主服务器

59.217.250.226

root/topnet@123

3306

8.0.32

8.0.35

从服务器

59.217.250.227

root/topnet@123

3306

8.0.32

8.0.35

升级步骤

关键步骤如下：

1.断开应用 2.确定主从无延时 3.关闭主库 4.关闭从库 5.备份主库数据目录 6.启动主库 7.启动备库 8.查看主从同步状态

查数据库版本

主服务器数据库版本

  1. mysql> select version();
  2. +-----------+
  3. | version() |
  4. +-----------+
  5. | 8.0.32    |
  6. +-----------+
  7. 1 row in set (0.00 sec)
  8.  
  9. mysql> show variables like '%version%';
  10. +--------------------------+------------------------------+
  11. | Variable_name            | Value                        |
  12. +--------------------------+------------------------------+
  13. | admin_tls_version        | TLSv1.2,TLSv1.3              |
  14. | immediate_server_version | 999999                       |
  15. | innodb_version           | 8.0.32                       |
  16. | original_server_version  | 999999                       |
  17. | protocol_version         | 10                           |
  18. | replica_type_conversions |                              |
  19. | slave_type_conversions   |                              |
  20. | tls_version              | TLSv1.2,TLSv1.3              |
  21. | version                  | 8.0.32                       |
  22. | version_comment          | MySQL Community Server - GPL |
  23. | version_compile_machine  | x86_64                       |
  24. | version_compile_os       | Linux                        |
  25. | version_compile_zlib     | 1.2.13                       |
  26. +--------------------------+------------------------------+
  27. 13 rows in set (0.00 sec)
  28.  
  29.  
  30. mysql> exit
  31. Bye
  32. [root@localhost ~]# mysql -V
  33. mysql  Ver 8.0.32 for Linux on x86_64 (MySQL Community Server - GPL)

从服务器数据库版本

  1. mysql> select version();
  2. +-----------+
  3. | version() |
  4. +-----------+
  5. | 8.0.32    |
  6. +-----------+
  7. 1 row in set (0.00 sec)
  8.  
  9. mysql> show variables like '%version%';
  10. +--------------------------+------------------------------+
  11. | Variable_name            | Value                        |
  12. +--------------------------+------------------------------+
  13. | admin_tls_version        | TLSv1.2,TLSv1.3              |
  14. | immediate_server_version | 999999                       |
  15. | innodb_version           | 8.0.32                       |
  16. | original_server_version  | 999999                       |
  17. | protocol_version         | 10                           |
  18. | replica_type_conversions |                              |
  19. | slave_type_conversions   |                              |
  20. | tls_version              | TLSv1.2,TLSv1.3              |
  21. | version                  | 8.0.32                       |
  22. | version_comment          | MySQL Community Server - GPL |
  23. | version_compile_machine  | x86_64                       |
  24. | version_compile_os       | Linux                        |
  25. | version_compile_zlib     | 1.2.13                       |
  26. +--------------------------+------------------------------+
  27. 13 rows in set (0.01 sec)
  28.  
  29. mysql> exit
  30. Bye
  31. [root@xzdcweb1 ~]# mysql -V
  32. mysql  Ver 8.0.32 for Linux on x86_64 (MySQL Community Server - GPL)

停止应用

确保业务已停止，保证数据一致性

  1. --查询数据库中哪些线程正在执行
  2. show processlist;

确定主从无延时

  1. --查询主库
  2. mysql> show master status;
  3. +---------------+-----------+--------------+------------------+-------------------+
  4. | File          | Position  | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
  5. +---------------+-----------+--------------+------------------+-------------------+
  6. | binlog.000002 | 568694023 |              |                  |                   |
  7. +---------------+-----------+--------------+------------------+-------------------+
  8. 1 row in set (0.00 sec)
  9.  
  10. --查询备库
  11. mysql> show slave status\G
  12. *************************** 1. row ***************************
  13.                Slave_IO_State: Waiting for source to send event
  14.                   Master_Host: 59.217.250.226
  15.                   Master_User: slave
  16.                   Master_Port: 3306
  17.                 Connect_Retry: 60
  18.               Master_Log_File: binlog.000002
  19.           Read_Master_Log_Pos: 568627797
  20.                Relay_Log_File: xzdcweb1-relay-bin.000037
  21.                 Relay_Log_Pos: 568474217
  22.         Relay_Master_Log_File: binlog.000002
  23.              Slave_IO_Running: Yes
  24.             Slave_SQL_Running: Yes
  25.               Replicate_Do_DB:
  26.           Replicate_Ignore_DB:
  27.            Replicate_Do_Table:
  28.        Replicate_Ignore_Table:
  29.       Replicate_Wild_Do_Table:
  30.   Replicate_Wild_Ignore_Table:
  31.                    Last_Errno: 0
  32.                    Last_Error:
  33.                  Skip_Counter: 0
  34.           Exec_Master_Log_Pos: 568627797
  35.               Relay_Log_Space: 568474776
  36.               Until_Condition: None
  37.                Until_Log_File:
  38.                 Until_Log_Pos: 0
  39.            Master_SSL_Allowed: No
  40.            Master_SSL_CA_File:
  41.            Master_SSL_CA_Path:
  42.               Master_SSL_Cert:
  43.             Master_SSL_Cipher:
  44.                Master_SSL_Key:
  45.         Seconds_Behind_Master: 0
  46. Master_SSL_Verify_Server_Cert: No
  47.                 Last_IO_Errno: 0
  48.                 Last_IO_Error:
  49.                Last_SQL_Errno: 0
  50.                Last_SQL_Error:
  51.   Replicate_Ignore_Server_Ids:
  52.              Master_Server_Id: 1
  53.                   Master_UUID: 5e53417b-c152-11ed-b3a0-a8494daa8138
  54.              Master_Info_File: mysql.slave_master_info
  55.                     SQL_Delay: 0
  56.           SQL_Remaining_Delay: NULL
  57.       Slave_SQL_Running_State: Replica has read all relay log; waiting for more updates
  58.            Master_Retry_Count: 86400

1.如下的两参数代表主从同步能够正常的运行网络

若是是Slave_IO_Running的参数不是YES则说明从库读取不到主库的bin-log日志,

若是是Slave_SQL_Running参数不是YES，则说明从库在读取中继日志发生问题。

Slave_IO_Running: Yes

Slave_SQL_Running: Yes同步

2.查看Seconds_Behind_Master参数，若是为0的话，则表示主从复制良好

原文链接：mysql如何准确判断主从延时情况_判断mysql主从延时的方法(不依赖中间件)-CSDN博客

停止数据库

停止从库
  1. --查正在运行的MySQL进程,确定安装目录和数据目录
  2. [root@xzdcweb1 ~]# ps -ef | grep mysql
  3. root     33033     1  0  2023 ?        00:00:00 /bin/sh /usr/local/mysql/bin/mysqld_safe --datadir=/data1/mysql/data --pid-file=/data1/mysql/data/xzdcweb1.pid
  4. mysql    33383 33033  0  2023 ?        03:44:54 /usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql --datadir=/data1/mysql/data --plugin-dir=/usr/local/mysql/lib/plugin --user=mysql --log-error=xzdcweb1.err --pid-file=/data1/mysql/data/xzdcweb1.pid --socket=/var/lib/mysql/mysql.sock
  5. root     50368 45094  0 20:01 pts/0    00:00:00 grep --color=auto mysql
  6. [root@xzdcweb1 ~]# systemctl status mysqld
  7. ● mysqld.service - LSB: start and stop MySQL
  8.    Loaded: loaded (/etc/rc.d/init.d/mysqld; bad; vendor preset: disabled)
  9.    Active: active (running) since Mon 2023-09-18 12:41:33 CST; 3 months 21 days ago
  10.      Docs: man:systemd-sysv-generator(8)
  11.     Tasks: 83
  12.    CGroup: /system.slice/mysqld.service
  13.            ├─33033 /bin/sh /usr/local/mysql/bin/mysqld_safe --datadir=/data1/mysql/data --pid-file=/data1/mysql/data/xzdcweb1.pid
  14.            └─33383 /usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql --datadir=/data1/mysql/data --plugin-dir=/usr/local/mysql/lib/plugin -...
  15.  
  16. Sep 18 12:41:27 xzdcweb1 systemd[1]: Starting LSB: start and stop MySQL...
  17. Sep 18 12:41:33 xzdcweb1 mysqld[33022]: Starting MySQL......[  OK  ]
  18. Sep 18 12:41:33 xzdcweb1 systemd[1]: Started LSB: start and stop MySQL.
  19.  
  20. --查看从同步情况
  21. show slave status\G
  22.  
  23. --停止同步
  24. mysql>stop slave;
  25.  
  26. --停止数据库
  27. --1.进入原8.0.23 mysql命令行 正确关闭数据库
  28. 法1：
  29. mysqladmin -uroot -p shutdown
  30.  
  31. [root@mysql]# mysql -uroot -p
  32. Enter password: 
  33. #使用mysql shell 命令util.checkForServerUpgrade('root@127.0.0.1:3307', {"password":"XXXXX", "targetVersion":"8.0.26", "configPath":"/etc/my3307.cnf"}) 检查升级到目标版本8.0.26，确认没有error级别的问题
  34. Errors:   0
  35. Warnings: 17
  36. Notices:  0
  37.  
  38. #检查没有未提交的xa事务
  39. mysql> xa recover;
  40. Empty set (0.00 sec)
  41.  
  42. # 确保数据都刷到硬盘上，更改成0
  43. mysql> show variables like 'innodb_fast_shutdown';
  44. +----------------------+-------+
  45. | Variable_name        | Value |
  46. +----------------------+-------+
  47. | innodb_fast_shutdown | 1     |
  48. +----------------------+-------+
  49. 1 row in set (0.00 sec)
  50.  
  51. mysql> set global innodb_fast_shutdown=0;
  52. Query OK, 0 rows affected (0.00 sec)
  53. mysql> show variables like 'innodb_fast_shutdown';
  54. +----------------------+-------+
  55. | Variable_name        | Value |
  56. +----------------------+-------+
  57. | innodb_fast_shutdown | 0     |
  58. +----------------------+-------+
  59.  
  60. mysql> shutdown;
  61. Query OK, 0 rows affected (0.00 sec)
  62.  
  63. mysql> exit
  64.  
  65. #查看进程和端口后，已无输出记录，确保已停止
  66. [root@xzdcweb1 ~]# ps -ef | grep mysql
  67. root     51658 45094  0 20:07 pts/0    00:00:00 grep --color=auto mysql
  68. [root@xzdcweb1 ~]# ss -nlt | grep 3306
停止主库
  1. --查正在运行的MySQL进程,确定安装目录和数据目录
  2. [root@localhost ~]# ps -ef | grep mysql
  3. root     41766 41065  0 19:52 pts/0    00:00:00 grep --color=auto mysql
  4. root     45742     1  0  2023 ?        00:00:00 /bin/sh /usr/local/mysql/bin/mysqld_safe --datadir=/data1/mysql/data --pid-file=/data1/mysql/data/localhost.localdomain.pid
  5. mysql    46126 45742  0  2023 ?        04:18:50 /usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql --datadir=/data1/mysql/data --plugin-dir=/usr/local/mysql/lib/plugin --user=mysql --log-error=localhost.localdomain.err --pid-file=/data1/mysql/data/localhost.localdomain.pid --socket=/var/lib/mysql/mysql.sock
  6. [root@localhost ~]# systemctl status mysqld
  7. ● mysqld.service - LSB: start and stop MySQL
  8.    Loaded: loaded (/etc/rc.d/init.d/mysqld; bad; vendor preset: disabled)
  9.    Active: active (running) since Mon 2023-09-18 11:54:00 CST; 3 months 22 days ago
  10.      Docs: man:systemd-sysv-generator(8)
  11.    CGroup: /system.slice/mysqld.service
  12.            ├─45742 /bin/sh /usr/local/mysql/bin/mysqld_safe --datadir=/data1/mysql/data --pid-file=/data1/mysql/data/localhost.localdomain.pid
  13.            └─46126 /usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql --datadir=/data1/mysql/data --plugin-dir=/usr/local/mysql/lib/plugin -...
  14.  
  15. Sep 18 11:53:57 localhost.localdomain systemd[1]: Starting LSB: start and stop MySQL...
  16. Sep 18 11:54:00 localhost.localdomain mysqld[45731]: Starting MySQL... SUCCESS!
  17. Sep 18 11:54:00 localhost.localdomain systemd[1]: Started LSB: start and stop MySQL.
  18.  
  19. --记下输出中的 File 和 Position 值，后续在从服务器上使用。
  20.  
  21. mysql> show master status;
  22. +---------------+-----------+--------------+------------------+-------------------+
  23. | File          | Position  | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
  24. +---------------+-----------+--------------+------------------+-------------------+
  25. | binlog.000002 | 568694023 |              |                  |                   |
  26. +---------------+-----------+--------------+------------------+-------------------+
  27. 1 row in set (0.00 sec)
  28.  
  29.  
  30. 参数说明：
  31. Binlog_Do_DB：同步数据库
  32. Binlog_Ignore_DB：忽略数据库即不同步的数据库
  33. Executed_Gtid_Set：空
  34.  
  35.  
  36. --停止数据库
  37. --1.进入原8.0.23 mysql命令行 正确关闭数据库
  38. 法1：
  39. mysqladmin -uroot -p shutdown
  40.  
  41. [root@mysql]# mysql -uroot -p
  42. Enter password: 
  43. #使用mysql shell 命令util.checkForServerUpgrade('root@127.0.0.1:3307', {"password":"XXXXX", "targetVersion":"8.0.26", "configPath":"/etc/my3307.cnf"}) 检查升级到目标版本8.0.26，确认没有error级别的问题
  44. Errors:   0
  45. Warnings: 17
  46. Notices:  0
  47.  
  48. #检查没有未提交的xa事务
  49. mysql> xa recover;
  50. Empty set (0.00 sec)
  51.  
  52. # 确保数据都刷到硬盘上，更改成0
  53. mysql> show variables like 'innodb_fast_shutdown';
  54. +----------------------+-------+
  55. | Variable_name        | Value |
  56. +----------------------+-------+
  57. | innodb_fast_shutdown | 1     |
  58. +----------------------+-------+
  59. 1 row in set (0.00 sec)
  60.  
  61. mysql> set global innodb_fast_shutdown=0;
  62. Query OK, 0 rows affected (0.00 sec)
  63. mysql> show variables like 'innodb_fast_shutdown';
  64. +----------------------+-------+
  65. | Variable_name        | Value |
  66. +----------------------+-------+
  67. | innodb_fast_shutdown | 0     |
  68. +----------------------+-------+
  69.  
  70. mysql> shutdown;
  71. Query OK, 0 rows affected (0.00 sec)
  72.  
  73. mysql> exit
  74.  
  75. #查看进程和端口后，已无输出记录，确保已停止
  76. [root@localhost ~]# ps -ef | grep mysql
  77. root     43674 43397  0 20:04 pts/0    00:00:00 grep --color=auto mysql
  78. [root@localhost ~]# ss -ntl | grep 3306

主库升级

主服务器备份安装目录

  1. --查正在运行的MySQL使用的配置文件
  2. [root@localhost ~]# cat /etc/my.cnf | grep basedir
  3. basedir=/usr/local/mysql  #安装目录
  4. [root@localhost ~]# cat /etc/my.cnf | grep datadir
  5. datadir=/data1/mysql/data #数据目录
  6.  
  7.  
  8. --备份安装目录
  9. [root@localhost ~]# cd /usr/local/
  10. [root@localhost local]# ls -l
  11. total 0
  12. drwxr-xr-x.  2 root root   6 Apr 11  2018 bin
  13. drwxr-xr-x.  2 root root   6 Apr 11  2018 etc
  14. drwxr-xr-x.  2 root root   6 Apr 11  2018 games
  15. drwxrwxr-x. 11 root root 208 Jul 31 23:00 hb
  16. drwxr-xr-x.  2 root root   6 Apr 11  2018 include
  17. drwxr-xr-x.  2 root root   6 Apr 11  2018 lib
  18. drwxr-xr-x.  2 root root   6 Apr 11  2018 lib64
  19. drwxr-xr-x.  2 root root   6 Apr 11  2018 libexec
  20. lrwxrwxrwx.  1 root root  42 Mar 22  2023 mysql -> /data1/mysql-8.0.32-linux-glibc2.12-x86_64
  21. drwxr-xr-x.  2 root root   6 Jul 31 23:00 process_temp
  22. drwxr-xr-x.  2 root root   6 Apr 11  2018 sbin
  23. drwxr-xr-x.  5 root root  49 Dec 15  2022 share
  24. drwxr-xr-x.  2 root root   6 Apr 11  2018 src
  25.  
  26.  
  27. [root@localhost data1]# cp -r mysql-8.0.32-linux-glibc2.12-x86_64 mysql-8.0.32-linux-glibc2.12-x86_64_bak_`date +%F`
  28. [root@localhost data1]# ls -l
  29. total 428996
  30. -rwxr--r--. 1 root  root       1530 Sep 18 15:43 bf.sh
  31. drwxr-xr-x. 2 root  root        278 Jan  9 01:00 db
  32. drwxr-xr-x. 2 root  root         27 Jan  9 01:00 log
  33. drwxr-xr-x. 4 mysql mysql        29 Mar 13  2023 mysql
  34. drwxr-xr-x. 9 root  root        129 Mar 22  2023 mysql-8.0.32-linux-glibc2.12-x86_64
  35. drwxr-xr-x. 9 root  root        129 Jan  9 20:11 mysql-8.0.32-linux-glibc2.12-x86_64_bak_2024-01-09
  36. -rw-r--r--. 1 root  root  439284500 Jan  8 17:12 mysql-8.0.35-linux-glibc2.17-x86_64.tar.xz
  37.  
  38. --删除/usr/local目录下mysql的软链接
  39. lrwxrwxrwx.  1 root root  42 Mar 22  2023 mysql -> /data1/mysql-8.0.32-linux-glibc2.12-x86_64
  40.  
  41. [root@localhost local]# unlink mysql
  42. [root@localhost local]# ls -l
  43. total 0
  44. drwxr-xr-x.  2 root root   6 Apr 11  2018 bin
  45. drwxr-xr-x.  2 root root   6 Apr 11  2018 etc
  46. drwxr-xr-x.  2 root root   6 Apr 11  2018 games
  47. drwxrwxr-x. 11 root root 208 Jul 31 23:00 hb
  48. drwxr-xr-x.  2 root root   6 Apr 11  2018 include
  49. drwxr-xr-x.  2 root root   6 Apr 11  2018 lib
  50. drwxr-xr-x.  2 root root   6 Apr 11  2018 lib64
  51. drwxr-xr-x.  2 root root   6 Apr 11  2018 libexec
  52. drwxr-xr-x.  2 root root   6 Jul 31 23:00 process_temp
  53. drwxr-xr-x.  2 root root   6 Apr 11  2018 sbin
  54. drwxr-xr-x.  5 root root  49 Dec 15  2022 share
  55. drwxr-xr-x.  2 root root   6 Apr 11  2018 src
  56.  
  57. 不建议rm mysql（规避误操作）

主服务器备份数据目录

  1. --备份数据目录
  2. [root@localhost mysql]# cd /data1/mysql
  3. [root@localhost mysql]# ls -l
  4. total 4
  5. drwxr-xr-x. 10 mysql mysql 4096 Jan  9 20:04 data
  6. drwxr-xr-x.  2 mysql mysql    6 Jan  8 23:54 tmp
  7. [root@localhost mysql]# cp -r data data_bak_`date +%F`
  8. [root@localhost mysql]# ls -l
  9. total 8
  10. drwxr-xr-x. 10 mysql mysql 4096 Jan  9 20:04 data
  11. drwxr-xr-x. 10 root  root  4096 Jan  9 20:14 data_bak_2024-01-09
  12. drwxr-xr-x.  2 mysql mysql    6 Jan  8 23:54 tmp
  13. [root@localhost mysql]# du -sh data
  14. 14G     data
  15. [root@localhost mysql]# du -sh data_bak_2024-01-09/
  16. 14G     data_bak_2024-01-09/
  17. [root@localhost mysql]# chown -R mysql:mysql data_bak_2024-01-09/
  18. [root@localhost mysql]# ls -l
  19. total 8
  20. drwxr-xr-x. 10 mysql mysql 4096 Jan  9 20:04 data
  21. drwxr-xr-x. 10 mysql mysql 4096 Jan  9 20:14 data_bak_2024-01-09
  22. drwxr-xr-x.  2 mysql mysql    6 Jan  8 23:54 tmp

主服务器备份配置文件

  1. --备份配置文件
  2. [root@localhost mysql]# cp /etc/my.cnf /etc/my.cnf_bak_`date +%F`
  3. [root@localhost mysql]# ls -l /etc/my.cnf
  4. -rw-r--r--. 1 root root 782 Sep 18 11:53 /etc/my.cnf
  5. [root@localhost mysql]# ls -l /etc/my.cnf_bak_2024-01-09
  6. -rw-r--r--. 1 root root 782 Jan  9 20:15 /etc/my.cnf_bak_2024-01-09

安装数据库软件

上传mysql-8.0.35-linux-glibc2.17-x86_64.tar.xz至/data1

  1. --解压
  2. [root@localhost data1]# cd /data1
  3. [root@localhost data1]# tar -xvf mysql-8.0.35-linux-glibc2.17-x86_64.tar.xz -C /data1/
  4.  
  5. --/usr/local目录下创建mysql的软链接
  6. [root@localhost local]# ln -s /data1/mysql-8.0.35-linux-glibc2.17-x86_64 /usr/local/mysql
  7. [root@localhost local]# ls -l
  8. total 0
  9. drwxr-xr-x.  2 root root   6 Apr 11  2018 bin
  10. drwxr-xr-x.  2 root root   6 Apr 11  2018 etc
  11. drwxr-xr-x.  2 root root   6 Apr 11  2018 games
  12. drwxrwxr-x. 11 root root 208 Jul 31 23:00 hb
  13. drwxr-xr-x.  2 root root   6 Apr 11  2018 include
  14. drwxr-xr-x.  2 root root   6 Apr 11  2018 lib
  15. drwxr-xr-x.  2 root root   6 Apr 11  2018 lib64
  16. drwxr-xr-x.  2 root root   6 Apr 11  2018 libexec
  17. lrwxrwxrwx.  1 root root  42 Jan  9 20:25 mysql -> /data1/mysql-8.0.35-linux-glibc2.17-x86_64
  18. drwxr-xr-x.  2 root root   6 Jul 31 23:00 process_temp
  19. drwxr-xr-x.  2 root root   6 Apr 11  2018 sbin
  20. drwxr-xr-x.  5 root root  49 Dec 15  2022 share
  21. drwxr-xr-x.  2 root root   6 Apr 11  2018 src

启动主库

查看版本
  1. [root@localhost local]# mysql -V
  2. mysql  Ver 8.0.35 for Linux on x86_64 (MySQL Community Server - GPL)

显示是新版本的版本号，升级成功

启动主库

建议重开一会话启动服务

  1. [root@localhost init.d]# systemctl start mysqld
  2. [root@localhost init.d]# systemctl status mysqld
  3. ● mysqld.service - LSB: start and stop MySQL
  4.    Loaded: loaded (/etc/rc.d/init.d/mysqld; bad; vendor preset: disabled)
  5.    Active: active (running) since Tue 2024-01-09 20:40:36 CST; 2s ago
  6.      Docs: man:systemd-sysv-generator(8)
  7.   Process: 48107 ExecStop=/etc/rc.d/init.d/mysqld stop (code=exited, status=0/SUCCESS)
  8.   Process: 49535 ExecStart=/etc/rc.d/init.d/mysqld start (code=exited, status=0/SUCCESS)
  9.    CGroup: /system.slice/mysqld.service
  10.            ├─49546 /bin/sh /usr/local/mysql/bin/mysqld_safe --datadir=/data1/mysql/data --pid-file=/data1/mysql/data/localhost.localdomain.pid
  11.            └─49915 /usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql --datadir=/data1/mysql/data --plugin-dir=/usr/local/mysql/lib/plugin -...
  12.  
  13. Jan 09 20:40:28 localhost.localdomain systemd[1]: Starting LSB: start and stop MySQL...
  14. Jan 09 20:40:36 localhost.localdomain mysqld[49535]: Starting MySQL........ SUCCESS!
  15. Jan 09 20:40:36 localhost.localdomain systemd[1]: Started LSB: start and stop MySQL.
  16. [root@localhost init.d]# ps -ef | grep mysql
  17. root     49546     1  0 20:40 ?        00:00:00 /bin/sh /usr/local/mysql/bin/mysqld_safe --datadir=/data1/mysql/data --pid-file=/data1/mysql/data/localhost.localdomain.pid
  18. mysql    49915 49546 36 20:40 ?        00:00:08 /usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql --datadir=/data1/mysql/data --plugin-dir=/usr/local/mysql/lib/plugin --user=mysql --log-error=localhost.localdomain.err --pid-file=/data1/mysql/data/localhost.localdomain.pid --socket=/var/lib/mysql/mysql.sock
  19. root     50341 48949  0 20:40 pts/0    00:00:00 grep --color=auto mysql
问题处理
启库服务运行但是未加载目录和配置文件
  1. --问题描述
  2. 升级后启库后查看服务状态不正常
  3. [root@xzdcweb1 data1]# mysql -V
  4. mysql  Ver 8.0.35 for Linux on x86_64 (MySQL Community Server - GPL)
  5. [root@xzdcweb1 ~]# systemctl start mysqld
  6. [root@xzdcweb1 ~]# systemctl status mysqld
  7. ● mysqld.service - LSB: start and stop MySQL
  8.    Loaded: loaded (/etc/rc.d/init.d/mysqld; bad; vendor preset: disabled)
  9.    Active: active (exited) since Mon 2023-09-18 12:41:33 CST; 3 months 22 days ago
  10.      Docs: man:systemd-sysv-generator(8)
  11.  
  12. Sep 18 12:41:27 xzdcweb1 systemd[1]: Starting LSB: start and stop MySQL...
  13. Sep 18 12:41:33 xzdcweb1 mysqld[33022]: Starting MySQL......[  OK  ]
  14. Sep 18 12:41:33 xzdcweb1 systemd[1]: Started LSB: start and stop MySQL.
  15. [root@xzdcweb1 ~]# ps -ef | grep mysql
  16. root     64535 64356  0 21:10 pts/1    00:00:00 grep --color=auto mysql
  17.  
  18. 以下是升级前关库前查看的服务和进程：
  19. [root@localhost ~]# ps -ef | grep mysql
  20. root     41766 41065  0 19:52 pts/0    00:00:00 grep --color=auto mysql
  21. root     45742     1  0  2023 ?        00:00:00 /bin/sh /usr/local/mysql/bin/mysqld_safe --datadir=/data1/mysql/data --pid-file=/data1/mysql/data/localhost.localdomain.pid
  22. mysql    46126 45742  0  2023 ?        04:18:50 /usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql --datadir=/data1/mysql/data --plugin-dir=/usr/local/mysql/lib/plugin --user=mysql --log-error=localhost.localdomain.err --pid-file=/data1/mysql/data/localhost.localdomain.pid --socket=/var/lib/mysql/mysql.sock
  23. [root@localhost ~]# systemctl status mysqld
  24. ● mysqld.service - LSB: start and stop MySQL
  25.    Loaded: loaded (/etc/rc.d/init.d/mysqld; bad; vendor preset: disabled)
  26.    Active: active (running) since Mon 2023-09-18 11:54:00 CST; 3 months 22 days ago
  27.      Docs: man:systemd-sysv-generator(8)
  28.    CGroup: /system.slice/mysqld.service
  29.            ├─45742 /bin/sh /usr/local/mysql/bin/mysqld_safe --datadir=/data1/mysql/data --pid-file=/data1/mysql/data/localhost.localdomain.pid
  30.            └─46126 /usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql --datadir=/data1/mysql/data --plugin-dir=/usr/local/mysql/lib/plugin -...
  31.  
  32. Sep 18 11:53:57 localhost.localdomain systemd[1]: Starting LSB: start and stop MySQL...
  33. Sep 18 11:54:00 localhost.localdomain mysqld[45731]: Starting MySQL... SUCCESS!
  34. Sep 18 11:54:00 localhost.localdomain systemd[1]: Started LSB: start and stop MySQL.
  35.  
  36. --问题原因
  37. mysql8.0.13之后启库时会自动进行升级字典，需要一些时间
  38.  
  39. --解决办法
  40. 缓一会儿再启动就正常了
  41.  
  42. 可查看日志，时间关系未进行查看
  43. 如果配置文件中无日志文件参数，默认在数据目录下
查看主库信息
  1. mysql> show master status;
  2. +---------------+----------+--------------+------------------+-------------------+
  3. | File          | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
  4. +---------------+----------+--------------+------------------+-------------------+
  5. | binlog.000003 |     7562 |              |                  |                   |
  6. +---------------+----------+--------------+------------------+-------------------+
  7. 1 row in set (0.00 sec)

备库升级

备服务器备份安装目录

  1. --查正在运行的MySQL使用的配置文件
  2. [root@xzdcweb1 ~]# cat /etc/my.cnf | grep basedir
  3. basedir=/usr/local/mysql   #mysql安装根目录
  4. [root@xzdcweb1 ~]# cat /etc/my.cnf | grep datadir
  5. datadir=/data1/mysql/data  #mysql数据文件所在目录
  6.  
  7.  
  8. --备份安装目录
  9. [root@xzdcweb1 ~]# cd /usr/local/
  10. [root@xzdcweb1 local]# ls -l
  11. total 0
  12. drwxr-xr-x   6 root root  58 Jul  5  2021 apr
  13. drwxr-xr-x.  2 root root 177 Dec 29 10:40 bin
  14. drwxr-xr-x.  2 root root 225 Jun  9  2023 etc
  15. drwxr-xr-x.  2 root root   6 Apr 11  2018 games
  16. drwxrwxr-x  11 root root 208 Jul 31 23:00 hb
  17. drwxr-xr-x.  3 root root  21 Dec 29 10:40 include
  18. drwxr-xr-x.  2 root root   6 Apr 11  2018 lib
  19. drwxr-xr-x.  4 root root 159 Dec 29 10:40 lib64
  20. drwxr-xr-x.  2 root root  90 Jun  9  2023 libexec
  21. lrwxrwxrwx   1 root root  42 Mar 22  2023 mysql -> /data1/mysql-8.0.32-linux-glibc2.12-x86_64
  22. drwxr-xr-x  11 root root 151 Jul  3  2021 nginx
  23. drwxr-xr-x   2 root root   6 Jul 31 23:00 process_temp
  24. drwxr-xr-x   6 root root  56 Jul  3  2021 python3
  25. drwxr-xr-x.  2 root root  18 Jun  9  2023 sbin
  26. drwxr-xr-x.  6 root root  60 Dec 29 10:41 share
  27. drwxr-xr-x.  3 root root  47 Jul  5  2021 src
  28. drwxr-xr-x   5 root root 140 Dec 29 10:40 ssl
  29.  
  30. [root@xzdcweb1 local]# cd /data1
  31. [root@xzdcweb1 data1]# ls -l
  32. total 428992
  33. drwxr-xr-x 2 rsync rsync         6 Sep 18 19:04 fdfsbak
  34. drwxr-xr-x 4 mysql mysql        29 Mar 13  2023 mysql
  35. drwxr-xr-x 9 root  root        129 Mar 22  2023 mysql-8.0.32-linux-glibc2.12-x86_64
  36. -rw-r--r-- 1 root  root  439284500 Jan  8 17:17 mysql-8.0.35-linux-glibc2.17-x86_64.tar.xz
  37. [root@xzdcweb1 data1]# cp -r mysql-8.0.32-linux-glibc2.12-x86_64 mysql-8.0.32-linux-glibc2.12-x86_64_bak_`date +%F`
  38. [root@xzdcweb1 data1]# ls -l
  39. total 428992
  40. drwxr-xr-x 2 rsync rsync         6 Sep 18 19:04 fdfsbak
  41. drwxr-xr-x 4 mysql mysql        29 Mar 13  2023 mysql
  42. drwxr-xr-x 9 root  root        129 Mar 22  2023 mysql-8.0.32-linux-glibc2.12-x86_64
  43. drwxr-xr-x 9 root  root        129 Jan  9 20:57 mysql-8.0.32-linux-glibc2.12-x86_64_bak_2024-01-09
  44. -rw-r--r-- 1 root  root  439284500 Jan  8 17:17 mysql-8.0.35-linux-glibc2.17-x86_64.tar.xz
  45.  
  46. --删除软链接
  47. [root@xzdcweb1 data1]# cd /usr/local/
  48. [root@xzdcweb1 local]# ls -l
  49. total 0
  50. drwxr-xr-x   6 root root  58 Jul  5  2021 apr
  51. drwxr-xr-x.  2 root root 177 Dec 29 10:40 bin
  52. drwxr-xr-x.  2 root root 225 Jun  9  2023 etc
  53. drwxr-xr-x.  2 root root   6 Apr 11  2018 games
  54. drwxrwxr-x  11 root root 208 Jul 31 23:00 hb
  55. drwxr-xr-x.  3 root root  21 Dec 29 10:40 include
  56. drwxr-xr-x.  2 root root   6 Apr 11  2018 lib
  57. drwxr-xr-x.  4 root root 159 Dec 29 10:40 lib64
  58. drwxr-xr-x.  2 root root  90 Jun  9  2023 libexec
  59. lrwxrwxrwx   1 root root  42 Mar 22  2023 mysql -> /data1/mysql-8.0.32-linux-glibc2.12-x86_64
  60. drwxr-xr-x  11 root root 151 Jul  3  2021 nginx
  61. drwxr-xr-x   2 root root   6 Jul 31 23:00 process_temp
  62. drwxr-xr-x   6 root root  56 Jul  3  2021 python3
  63. drwxr-xr-x.  2 root root  18 Jun  9  2023 sbin
  64. drwxr-xr-x.  6 root root  60 Dec 29 10:41 share
  65. drwxr-xr-x.  3 root root  47 Jul  5  2021 src
  66. drwxr-xr-x   5 root root 140 Dec 29 10:40 ssl
  67. [root@xzdcweb1 local]# unlink mysql
  68. [root@xzdcweb1 local]# ls -l
  69. total 0
  70. drwxr-xr-x   6 root root  58 Jul  5  2021 apr
  71. drwxr-xr-x.  2 root root 177 Dec 29 10:40 bin
  72. drwxr-xr-x.  2 root root 225 Jun  9  2023 etc
  73. drwxr-xr-x.  2 root root   6 Apr 11  2018 games
  74. drwxrwxr-x  11 root root 208 Jul 31 23:00 hb
  75. drwxr-xr-x.  3 root root  21 Dec 29 10:40 include
  76. drwxr-xr-x.  2 root root   6 Apr 11  2018 lib
  77. drwxr-xr-x.  4 root root 159 Dec 29 10:40 lib64
  78. drwxr-xr-x.  2 root root  90 Jun  9  2023 libexec
  79. drwxr-xr-x  11 root root 151 Jul  3  2021 nginx
  80. drwxr-xr-x   2 root root   6 Jul 31 23:00 process_temp
  81. drwxr-xr-x   6 root root  56 Jul  3  2021 python3
  82. drwxr-xr-x.  2 root root  18 Jun  9  2023 sbin
  83. drwxr-xr-x.  6 root root  60 Dec 29 10:41 share
  84. drwxr-xr-x.  3 root root  47 Jul  5  2021 src
  85. drwxr-xr-x   5 root root 140 Dec 29 10:40 ssl
  86.  
  87. 注意：
  88. 不建议rm mysql (规避误操作)

备服务器备份数据目录

  1. --备份数据目录
  2. [root@xzdcweb1 local]# cd /data1/mysql
  3. [root@xzdcweb1 mysql]# ls -l
  4. total 4
  5. drwxr-xr-x 10 mysql mysql 4096 Jan  9 20:07 data
  6. drwxr-xr-x  2 mysql mysql    6 Sep 18 12:41 tmp
  7. [root@xzdcweb1 mysql]# du -sh data/
  8. 4.0G    data/
  9. [root@xzdcweb1 mysql]# cp -r data data_bak_`date +%F`
  10. [root@xzdcweb1 mysql]# ls -l
  11. total 8
  12. drwxr-xr-x 10 mysql mysql 4096 Jan  9 20:07 data
  13. drwxr-xr-x 10 root  root  4096 Jan  9 21:01 data_bak_2024-01-09
  14. drwxr-xr-x  2 mysql mysql    6 Sep 18 12:41 tmp
  15. [root@xzdcweb1 mysql]# du -sh data
  16. 4.0G    data
  17. [root@xzdcweb1 mysql]# du -sh data_bak_2024-01-09/
  18. 4.0G    data_bak_2024-01-09/

备服务器备份配置文件

  1. --备份配置文件
  2. [root@xzdcweb1 mysql]# cp /etc/my.cnf /etc/my.cnf_bak_`date +%F`
  3. [root@xzdcweb1 mysql]# ls -l /etc/my.cnf
  4. -rw-r--r-- 1 root root 747 Sep 18 12:16 /etc/my.cnf
  5. [root@xzdcweb1 mysql]# ls -l /etc/my.cnf_bak_2024-01-09
  6. -rw-r--r-- 1 root root 747 Jan  9 21:02 /etc/my.cnf_bak_2024-01-09

安装数据库软件

上传mysql-8.0.35-linux-glibc2.17-x86_64.tar.xz至/data1

  1. --解压
  2. [root@xzdcweb1 mysql]# cd /data1
  3. [root@xzdcweb1 data1]# ls -l
  4. total 428992
  5. drwxr-xr-x 2 rsync rsync         6 Sep 18 19:04 fdfsbak
  6. drwxr-xr-x 5 mysql mysql        56 Jan  9 21:01 mysql
  7. drwxr-xr-x 9 root  root        129 Mar 22  2023 mysql-8.0.32-linux-glibc2.12-x86_64
  8. drwxr-xr-x 9 root  root        129 Jan  9 20:57 mysql-8.0.32-linux-glibc2.12-x86_64_bak_2024-01-09
  9. -rw-r--r-- 1 root  root  439284500 Jan  8 17:17 mysql-8.0.35-linux-glibc2.17-x86_64.tar.xz
  10. [root@xzdcweb1 data1]# tar -xvf mysql-8.0.35-linux-glibc2.17-x86_64.tar.xz -C /data1/
  11.  
  12. [root@xzdcweb1 data1]# ls -l
  13. total 428992
  14. drwxr-xr-x 2 rsync rsync         6 Sep 18 19:04 fdfsbak
  15. drwxr-xr-x 5 mysql mysql        56 Jan  9 21:01 mysql
  16. drwxr-xr-x 9 root  root        129 Mar 22  2023 mysql-8.0.32-linux-glibc2.12-x86_64
  17. drwxr-xr-x 9 root  root        129 Jan  9 20:57 mysql-8.0.32-linux-glibc2.12-x86_64_bak_2024-01-09
  18. drwxr-xr-x 9 root  root        129 Jan  9 21:05 mysql-8.0.35-linux-glibc2.17-x86_64
  19. -rw-r--r-- 1 root  root  439284500 Jan  8 17:17 mysql-8.0.35-linux-glibc2.17-x86_64.tar.xz
  20.  
  21. --/usr/local目录下创建mysql目录的软链接
  22. [root@xzdcweb1 data1]# ln -s /data1/mysql-8.0.35-linux-glibc2.17-x86_64 /usr/local/mysql
  23. [root@xzdcweb1 data1]# ls -l /usr/local/
  24. total 0
  25. drwxr-xr-x   6 root root  58 Jul  5  2021 apr
  26. drwxr-xr-x.  2 root root 177 Dec 29 10:40 bin
  27. drwxr-xr-x.  2 root root 225 Jun  9  2023 etc
  28. drwxr-xr-x.  2 root root   6 Apr 11  2018 games
  29. drwxrwxr-x  11 root root 208 Jul 31 23:00 hb
  30. drwxr-xr-x.  3 root root  21 Dec 29 10:40 include
  31. drwxr-xr-x.  2 root root   6 Apr 11  2018 lib
  32. drwxr-xr-x.  4 root root 159 Dec 29 10:40 lib64
  33. drwxr-xr-x.  2 root root  90 Jun  9  2023 libexec
  34. lrwxrwxrwx   1 root root  42 Jan  9 21:08 mysql -> /data1/mysql-8.0.35-linux-glibc2.17-x86_64
  35. drwxr-xr-x  11 root root 151 Jul  3  2021 nginx
  36. drwxr-xr-x   2 root root   6 Jul 31 23:00 process_temp
  37. drwxr-xr-x   6 root root  56 Jul  3  2021 python3
  38. drwxr-xr-x.  2 root root  18 Jun  9  2023 sbin
  39. drwxr-xr-x.  6 root root  60 Dec 29 10:41 share
  40. drwxr-xr-x.  3 root root  47 Jul  5  2021 src
  41. drwxr-xr-x   5 root root 140 Dec 29 10:40 ssl

启动备库

查看版本
  1. [root@xzdcweb1 data1]# mysql -V
  2. mysql  Ver 8.0.35 for Linux on x86_64 (MySQL Community Server - GPL)

显示是新版本的版本号，升级成功

启动备库
  1. [root@xzdcweb1 ~]# systemctl start mysqld
  2. [root@xzdcweb1 ~]# systemctl status mysqld
  3. ● mysqld.service - LSB: start and stop MySQL
  4.    Loaded: loaded (/etc/rc.d/init.d/mysqld; bad; vendor preset: disabled)
  5.    Active: active (running) since Tue 2024-01-09 21:15:36 CST; 6s ago
  6.      Docs: man:systemd-sysv-generator(8)
  7.   Process: 64762 ExecStop=/etc/rc.d/init.d/mysqld stop (code=exited, status=0/SUCCESS)
  8.   Process: 65533 ExecStart=/etc/rc.d/init.d/mysqld start (code=exited, status=0/SUCCESS)
  9.     Tasks: 46
  10.    CGroup: /system.slice/mysqld.service
  11.            ├─345 /bin/sh /usr/local/mysql/bin/mysqld_safe --datadir=/data1/mysql/data --pid-file=/data1/mysql/data/xzdcweb1.pid
  12.            └─719 /usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql --datadir=/data1/mysql/data --plugin-dir=/usr/local/mysql/lib/plugin --u...
  13.  
  14. Jan 09 21:15:25 xzdcweb1 systemd[1]: Starting LSB: start and stop MySQL...
  15. Jan 09 21:15:36 xzdcweb1 mysqld[65533]: Starting MySQL...........[  OK  ]
  16. Jan 09 21:15:36 xzdcweb1 systemd[1]: Started LSB: start and stop MySQL.
  17. [root@xzdcweb1 ~]# ps -ef | grep mysql
  18. root       345     1  0 21:15 ?        00:00:00 /bin/sh /usr/local/mysql/bin/mysqld_safe --datadir=/data1/mysql/data --pid-file=/data1/mysql/data/xzdcweb1.pid
  19. mysql      719   345 55 21:15 ?        00:00:14 /usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql --datadir=/data1/mysql/data --plugin-dir=/usr/local/mysql/lib/plugin --user=mysql --log-error=xzdcweb1.err --pid-file=/data1/mysql/data/xzdcweb1.pid --socket=/var/lib/mysql/mysql.sock
  20. root      1147 64356  0 21:15 pts/1    00:00:00 grep --color=auto mysql
问题处理
启库服务运行但是未加载目录和配置文件
  1. --问题描述
  2. 升级后启库后查看服务状态不正常
  3. [root@xzdcweb1 data1]# mysql -V
  4. mysql  Ver 8.0.35 for Linux on x86_64 (MySQL Community Server - GPL)
  5. [root@xzdcweb1 ~]# systemctl start mysqld
  6. [root@xzdcweb1 ~]# systemctl status mysqld
  7. ● mysqld.service - LSB: start and stop MySQL
  8.    Loaded: loaded (/etc/rc.d/init.d/mysqld; bad; vendor preset: disabled)
  9.    Active: active (exited) since Mon 2023-09-18 12:41:33 CST; 3 months 22 days ago
  10.      Docs: man:systemd-sysv-generator(8)
  11.  
  12. Sep 18 12:41:27 xzdcweb1 systemd[1]: Starting LSB: start and stop MySQL...
  13. Sep 18 12:41:33 xzdcweb1 mysqld[33022]: Starting MySQL......[  OK  ]
  14. Sep 18 12:41:33 xzdcweb1 systemd[1]: Started LSB: start and stop MySQL.
  15. [root@xzdcweb1 ~]# ps -ef | grep mysql
  16. root     64535 64356  0 21:10 pts/1    00:00:00 grep --color=auto mysql
  17.  
  18. 以下是升级前关库前查看的服务和进程：
  19. [root@localhost ~]# ps -ef | grep mysql
  20. root     41766 41065  0 19:52 pts/0    00:00:00 grep --color=auto mysql
  21. root     45742     1  0  2023 ?        00:00:00 /bin/sh /usr/local/mysql/bin/mysqld_safe --datadir=/data1/mysql/data --pid-file=/data1/mysql/data/localhost.localdomain.pid
  22. mysql    46126 45742  0  2023 ?        04:18:50 /usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql --datadir=/data1/mysql/data --plugin-dir=/usr/local/mysql/lib/plugin --user=mysql --log-error=localhost.localdomain.err --pid-file=/data1/mysql/data/localhost.localdomain.pid --socket=/var/lib/mysql/mysql.sock
  23. [root@localhost ~]# systemctl status mysqld
  24. ● mysqld.service - LSB: start and stop MySQL
  25.    Loaded: loaded (/etc/rc.d/init.d/mysqld; bad; vendor preset: disabled)
  26.    Active: active (running) since Mon 2023-09-18 11:54:00 CST; 3 months 22 days ago
  27.      Docs: man:systemd-sysv-generator(8)
  28.    CGroup: /system.slice/mysqld.service
  29.            ├─45742 /bin/sh /usr/local/mysql/bin/mysqld_safe --datadir=/data1/mysql/data --pid-file=/data1/mysql/data/localhost.localdomain.pid
  30.            └─46126 /usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql --datadir=/data1/mysql/data --plugin-dir=/usr/local/mysql/lib/plugin -...
  31.  
  32. Sep 18 11:53:57 localhost.localdomain systemd[1]: Starting LSB: start and stop MySQL...
  33. Sep 18 11:54:00 localhost.localdomain mysqld[45731]: Starting MySQL... SUCCESS!
  34. Sep 18 11:54:00 localhost.localdomain systemd[1]: Started LSB: start and stop MySQL.
  35.  
  36. --问题原因
  37. mysql8.0.13之后启库时会自动进行升级字典，需要一些时间
  38.  
  39. --解决办法
  40. 缓一会儿再启动就正常了
  41.  
  42. 可查看日志，时间关系未进行查看
  43. 如果配置文件中无日志文件参数，默认在数据目录下
查看备库同步信息
  1. root@localhost :mysql 19:24:53>show slave status\G
  2. *************************** 1. row ***************************
  3.                Slave_IO_State: Waiting for source to send event
  4.                   Master_Host: 192.168.40.160
  5.                   Master_User: replication
  6.                   Master_Port: 2213
  7.                 Connect_Retry: 60
  8.               Master_Log_File: mybinlog.000003
  9.           Read_Master_Log_Pos: 157
  10.                Relay_Log_File: mysql-relay-bin.000005
  11.                 Relay_Log_Pos: 371
  12.         Relay_Master_Log_File: mybinlog.000003
  13.              Slave_IO_Running: Yes
  14.             Slave_SQL_Running: Yes
  15.               Replicate_Do_DB: 
  16.           Replicate_Ignore_DB: 
  17.            Replicate_Do_Table: 
  18.        Replicate_Ignore_Table: 
  19.       Replicate_Wild_Do_Table: 
  20.   Replicate_Wild_Ignore_Table: 
  21.                    Last_Errno: 0
  22.                    Last_Error: 
  23.                  Skip_Counter: 0
  24.           Exec_Master_Log_Pos: 157
  25.               Relay_Log_Space: 749
  26.               Until_Condition: None
  27.                Until_Log_File: 
  28.                 Until_Log_Pos: 0
  29.            Master_SSL_Allowed: No
  30.            Master_SSL_CA_File: 
  31.            Master_SSL_CA_Path: 
  32.               Master_SSL_Cert: 
  33.             Master_SSL_Cipher: 
  34.                Master_SSL_Key: 
  35.         Seconds_Behind_Master: 0
  36. Master_SSL_Verify_Server_Cert: No
  37.                 Last_IO_Errno: 0
  38.                 Last_IO_Error: 
  39.                Last_SQL_Errno: 0
  40.                Last_SQL_Error: 
  41.   Replicate_Ignore_Server_Ids: 
  42.              Master_Server_Id: 160
  43.                   Master_UUID: d9eafe5f-aeaf-11ee-a9c8-000c29528229
  44.              Master_Info_File: mysql.slave_master_info
  45.                     SQL_Delay: 0
  46.           SQL_Remaining_Delay: NULL
  47.       Slave_SQL_Running_State: Replica has read all relay log; waiting for more updates
  48.            Master_Retry_Count: 86400
  49.                   Master_Bind: 
  50.       Last_IO_Error_Timestamp: 
  51.      Last_SQL_Error_Timestamp: 
  52.                Master_SSL_Crl: 
  53.            Master_SSL_Crlpath: 
  54.            Retrieved_Gtid_Set: 
  55.             Executed_Gtid_Set: 
  56.                 Auto_Position: 0
  57.          Replicate_Rewrite_DB: 
  58.                  Channel_Name: 
  59.            Master_TLS_Version: 
  60.        Master_public_key_path: 
  61.         Get_master_public_key: 1
  62.             Network_Namespace: 
  63. 1 row in set, 1 warning (0.00 sec)

mysql从库默认启动时slave也会自动启动复制。

1.如下的两参数代表主从同步能够正常的运行网络

若是是Slave_IO_Running的参数不是YES则说明从库读取不到主库的bin-log日志

若是是Slave_SQL_Running参数不是YES，则说明从库在读取中继日志发生问题。

Slave_IO_Running: Yes

Slave_SQL_Running: Yes同步

2.查看Seconds_Behind_Master参数，若是为0的话，则表示主从复制良好

声明：本文内容由网友自发贡献，不代表【wpsshop博客】立场，版权归原作者所有，本站不承担相应法律责任。如您发现有侵权的内容，请联系我们。转载请注明出处：https://www.wpsshop.cn/w/菜鸟追梦旅行/article/detail/100731?site
推荐阅读
相关标签

Copyright © 2003-2013 www.wpsshop.cn 版权所有，并保留所有权利。

  

闽ICP备14008679号