百度快照是干嘛的,东营seo网站排名,如何设计一个有趣的网页,拉新项目官方一手平台目录 一、实验
1.环境
2.Mysql-shell 检查工具兼容性
3.逻辑备份MySQL数据
4.备份MySQL 数据目录、安装目录、配置文件
5.MySQL 升级
6.master节点 使用systemd管理mysql8
7. slave1 节点升级
8. slave2 节点升级
9.半同步设置
二、问题
1.mysqldump备份报错
2.Inn…目录 一、实验
1.环境
2.Mysql-shell 检查工具兼容性
3.逻辑备份MySQL数据
4.备份MySQL 数据目录、安装目录、配置文件
5.MySQL 升级
6.master节点 使用systemd管理mysql8
7. slave1 节点升级
8. slave2 节点升级
9.半同步设置
二、问题
1.mysqldump备份报错
2.InnoDB 有哪些关闭模式。
3.master节点执行升级程序报错
4. slave 节点执行升级程序报错
5.监视半同步复制的插件状态报错 一、实验
1.环境
1主机
表1 主机
架构当前版本目标版本IP备注MySQL Master5.7.428.2.0192.168.204.10主服务器MySQL Slave15.7.428.2.0192.168.204.11 从服务器 MySQL Slave25.7.428.2.0192.168.204.12从服务器 (2) 查看版本
Master
[rootlocalhost ~]# mysql -VSlave1
[rootlocalhost ~]# mysql -VSlave2
[rootlocalhost ~]# mysql -V3查看状态
Master
[rootlocalhost ~]# mysql -uroot -p
……
mysql show master status;
-------------------------------------------------------------------------------
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
-------------------------------------------------------------------------------
| mysql-bin.000007 | 1372 | | | |
-------------------------------------------------------------------------------
1 row in set (0.00 sec)mysql Slave1
[rootlocalhost ~]# mysql -uroot -p
……
mysql show slave status\G
*************************** 1. row ***************************Slave_IO_State: Waiting for master to send eventMaster_Host: 192.168.204.10Master_User: myslaveMaster_Port: 3306Connect_Retry: 60Master_Log_File: mysql-bin.000007Read_Master_Log_Pos: 1372Relay_Log_File: relay-log-bin.000047Relay_Log_Pos: 950Relay_Master_Log_File: mysql-bin.000007Slave_IO_Running: YesSlave_SQL_Running: YesReplicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0Last_Error: Skip_Counter: 0Exec_Master_Log_Pos: 1372Relay_Log_Space: 1956Until_Condition: NoneUntil_Log_File: Until_Log_Pos: 0Master_SSL_Allowed: NoMaster_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: NoLast_IO_Errno: 0Last_IO_Error: Last_SQL_Errno: 0Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 1Master_UUID: c8246fd9-1c99-11ee-af46-000c29747129Master_Info_File: /var/lib/mysql/master.infoSQL_Delay: 0SQL_Remaining_Delay: NULLSlave_SQL_Running_State: Slave has read all relay log; waiting for more updatesMaster_Retry_Count: 86400Master_Bind: Last_IO_Error_Timestamp: Last_SQL_Error_Timestamp: Master_SSL_Crl: Master_SSL_Crlpath: Retrieved_Gtid_Set: Executed_Gtid_Set: Auto_Position: 0Replicate_Rewrite_DB: Channel_Name: Master_TLS_Version:
1 row in set (0.00 sec)mysql Slave2
[rootlocalhost ~]# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 30
Server version: 5.7.42 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 show slave status\G
*************************** 1. row ***************************Slave_IO_State: Waiting for master to send eventMaster_Host: 192.168.204.10Master_User: myslaveMaster_Port: 3306Connect_Retry: 60Master_Log_File: mysql-bin.000007Read_Master_Log_Pos: 1372Relay_Log_File: relay-log-bin.000025Relay_Log_Pos: 320Relay_Master_Log_File: mysql-bin.000007Slave_IO_Running: YesSlave_SQL_Running: YesReplicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0Last_Error: Skip_Counter: 0Exec_Master_Log_Pos: 1372Relay_Log_Space: 1321Until_Condition: NoneUntil_Log_File: Until_Log_Pos: 0Master_SSL_Allowed: NoMaster_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: NoLast_IO_Errno: 0Last_IO_Error: Last_SQL_Errno: 0Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 1Master_UUID: c8246fd9-1c99-11ee-af46-000c29747129Master_Info_File: /var/lib/mysql/master.infoSQL_Delay: 0SQL_Remaining_Delay: NULLSlave_SQL_Running_State: Slave has read all relay log; waiting for more updatesMaster_Retry_Count: 86400Master_Bind: Last_IO_Error_Timestamp: Last_SQL_Error_Timestamp: Master_SSL_Crl: Master_SSL_Crlpath: Retrieved_Gtid_Set: Executed_Gtid_Set: Auto_Position: 0Replicate_Rewrite_DB: Channel_Name: Master_TLS_Version:
1 row in set (0.00 sec)mysql 4查看配置
Master
[rootlocalhost ~]# vim /etc/my.cnfSlave1
[rootlocalhost ~]# vim /etc/my.cnfSlave2
[rootlocalhost ~]# vim /etc/my.cnf(5)查看半同步是否在运行
master
mysql show status like Rpl_semi_sync_master_status;
------------------------------------
| Variable_name | Value |
------------------------------------
| Rpl_semi_sync_master_status | ON |
------------------------------------
1 row in set (0.00 sec)mysql show variables like rpl_semi_sync_master_timeout;
-------------------------------------
| Variable_name | Value |
-------------------------------------
| rpl_semi_sync_master_timeout | 1000 |
-------------------------------------
1 row in set (0.00 sec)slave1
mysql show status like Rpl_semi_sync_slave_status;
-----------------------------------
| Variable_name | Value |
-----------------------------------
| Rpl_semi_sync_slave_status | ON |
-----------------------------------
1 row in set (0.00 sec)slave2
mysql show status like Rpl_semi_sync_slave_status;
-----------------------------------
| Variable_name | Value |
-----------------------------------
| Rpl_semi_sync_slave_status | ON |
-----------------------------------
1 row in set (0.01 sec)2.Mysql-shell 检查工具兼容性 (1) 查看
https://downloads.mysql.com/archives/shell/ 最新版本为8.2.1 2查看 GLIBC 版本
master
[rootlocalhost ~]# ldd --version
ldd (GNU libc) 2.17
Copyright (C) 2012 Free Software Foundation, Inc.
This is free software; see the source for copying conditions. There is NO
warranty; not even for MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.
由 Roland McGrath 和 Ulrich Drepper 编写。 slave1
[rootlocalhost ~]# ldd --version
ldd (GNU libc) 2.17
Copyright (C) 2012 Free Software Foundation, Inc.
This is free software; see the source for copying conditions. There is NO
warranty; not even for MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.
由 Roland McGrath 和 Ulrich Drepper 编写。 slave2
[rootlocalhost ~]# ldd --version
ldd (GNU libc) 2.17
Copyright (C) 2012 Free Software Foundation, Inc.
This is free software; see the source for copying conditions. There is NO
warranty; not even for MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.
由 Roland McGrath 和 Ulrich Drepper 编写。 3下载最新版本所有主机
wget https://downloads.mysql.com/archives/get/p/43/file/mysql-shell-8.2.1-linux-glibc2.17-x86-64bit.tar.gz 4解压
[rootlocalhost ~]# tar -xf mysql-shell-8.2.1-linux-glibc2.17-x86-64bit.tar.gz 5 在两台slave服务器上关闭同步
mysql stop slave;6master测试
[rootlocalhost ~]# cd mysql-shell-8.2.1-linux-glibc2.17-x86-64bit/
[rootlocalhost mysql-shell-8.2.1-linux-glibc2.17-x86-64bit]# ls
bin lib libexec share
[rootlocalhost mysql-shell-8.2.1-linux-glibc2.17-x86-64bit]# cd bin
[rootlocalhost bin]# ls
mysql-secret-store-login-path mysqlsh执行
[rootlocalhost bin]# ./mysqlsh -uroot -p -S /var/lib/mysql/mysql.sock -e util.checkForServerUpgrade() util.checkForServerUpgrade.log
Please provide the password for root/var%2Flib%2Fmysql%2Fmysql.sock: ******
NOTE: No fatal errors were found that would prevent an upgrade, but some potential issues were detected. Please ensure that the reported issues are not significant before upgrading.7master查看报告
[rootlocalhost bin]# ls
mysql-secret-store-login-path mysqlsh util.checkForServerUpgrade.log
[rootlocalhost bin]# vim util.checkForServerUpgrade.log Community Server (GPL), will now be checked for compatibility issues for
upgrade to MySQL 8.2.1...1) Usage of old temporal typeNo issues found2) MySQL 8.0 syntax check for routine-like objectsNo issues found3) Usage of db objects with names conflicting with new reserved keywordsNo issues found4) Usage of utf8mb3 charsetNo issues found5) Table names in the mysql schema conflicting with new tables in 8.0No issues found6) Partitioned tables using engines with non native partitioningNo issues found7) Foreign key constraint names longer than 64 charactersNo issues found8) Usage of obsolete MAXDB sql_mode flagNo issues found9) Usage of obsolete sql_mode flagsNotice: The following DB objects have obsolete options persisted forsql_mode, which will be cleared during upgrade to 8.0.More information:https://dev.mysql.com/doc/refman/8.0/en/mysql-nutshell.html#mysql-nutshell-removalsglobal system variable sql_mode - defined using obsolete NO_AUTO_CREATE_USERoption10) ENUM/SET column definitions containing elements longer than 255 charactersNo issues found11) Usage of partitioned tables in shared tablespacesNo issues found12) Circular directory references in tablespace data file pathsNo issues found13) Usage of removed functionsNo issues found14) Usage of removed GROUP BY ASC/DESC syntaxNo issues found15) Removed system variables for error logging to the system log configurationTo run this check requires full path to MySQL server configuration file to be specified at configPath key of options dictionaryMore information:https://dev.mysql.com/doc/relnotes/mysql/8.0/en/news-8-0-13.html#mysqld-8-0-13-logging16) Removed system variablesTo run this check requires full path to MySQL server configuration file to be specified at configPath key of options dictionaryMore information:https://dev.mysql.com/doc/refman/8.0/en/added-deprecated-removed.html#optvars-removed17) System variables with new default valuesTo run this check requires full path to MySQL server configuration file to be specified at configPath key of options dictionaryMore information:https://mysqlserverteam.com/new-defaults-in-mysql-8-0/18) Zero Date, Datetime, and Timestamp valuesNo issues found19) Schema inconsistencies resulting from file removal or corruptionNo issues found20) Tables recognized by InnoDB that belong to a different engineNo issues found21) Issues reported by check table x for upgrade commandNo issues found22) New default authentication plugin considerationsWarning: The new default authentication plugin caching_sha2_password offersmore secure password hashing than previously used mysql_native_password(and consequent improved client connection authentication). However, it alsohas compatibility implications that may affect existing MySQL installations.If your MySQL installation must serve pre-8.0 clients and you encountercompatibility issues after upgrading, the simplest way to address thoseissues is to reconfigure the server to revert to the previous defaultauthentication plugin (mysql_native_password). For example, use these linesin the server option file:[mysqld]default_authentication_pluginmysql_native_passwordHowever, the setting should be viewed as temporary, not as a long term orpermanent solution, because it causes new accounts created with the settingin effect to forego the improved authentication security.If you are using replication please take time to understand how theauthentication plugin changes may impact you.More information:https://dev.mysql.com/doc/refman/8.0/en/upgrading-from-previous-series.html#upgrade-caching-sha2-password-compatibility-issueshttps://dev.mysql.com/doc/refman/8.0/en/upgrading-from-previous-series.html#upgrade-caching-sha2-password-replication23) Columns which cannot have default valuesNo issues found24) Check for invalid table names and schema names used in 5.7No issues found25) Check for orphaned routines in 5.7No issues found26) Check for deprecated usage of single dollar signs in object namesNo issues found27) Check for indexes that are too large to work on higher versions of MySQL
Server than 5.7No issues found28) Check for deprecated .table syntax used in routines.No issues found29) Check for columns that have foreign keys pointing to tables from a diffrent
database engine.No issues foundErrors: 0
Warnings: 1
Notices: 1
从输出报告可以看出升级检查器在29个方面进行了检查最终得出1个警告信息和1个提示。 (8) slave1 测试
[rootlocalhost ~]# tar -xf mysql-shell-8.2.1-linux-glibc2.17-x86-64bit.tar.gz
您在 /var/spool/mail/root 中有新邮件
[rootlocalhost ~]# cd mysql-shell-8.2.1-linux-glibc2.17-x86-64bit/bin
[rootlocalhost bin]# ./mysqlsh -uroot -p -S /var/lib/mysql/mysql.sock -e util.checkForServerUpgrade() util.checkForServerUpgrade.log
Please provide the password for root/var%2Flib%2Fmysql%2Fmysql.sock: ******
NOTE: No fatal errors were found that would prevent an upgrade, but some potential issues were detected. Please ensure that the reported issues are not significant before upgrading.
[rootlocalhost bin]# ls
mysql-secret-store-login-path mysqlsh util.checkForServerUpgrade.log
[rootlocalhost bin]# vim util.checkForServerUpgrade.log 从输出报告可以看出升级检查器在29个方面进行了检查最终得出1个警告信息和1个提示。 9slave2 测试
[rootlocalhost ~]# tar -xf mysql-shell-8.2.1-linux-glibc2.17-x86-64bit.tar.gz
[rootlocalhost ~]# cd mysql-shell-8.2.1-linux-glibc2.17-x86-64bit/bin
[rootlocalhost bin]# ./mysqlsh -uroot -p -S /var/lib/mysql/mysql.sock -e util.checkForServerUpgrade() util.checkForServerUpgrade.log
Please provide the password for root/var%2Flib%2Fmysql%2Fmysql.sock: ******
NOTE: No fatal errors were found that would prevent an upgrade, but some potential issues were detected. Please ensure that the reported issues are not significant before upgrading.
[rootlocalhost bin]# ls
mysql-secret-store-login-path mysqlsh util.checkForServerUpgrade.log
[rootlocalhost bin]# vim util.checkForServerUpgrade.log 从输出报告可以看出升级检查器在29个方面进行了检查最终得出1个警告信息和4个提示。 3.逻辑备份MySQL数据
1 查看数据库
master
其内置的四个数据库mysql、information_schema、sys和performance_schema
mysql show databases;
--------------------
| Database |
--------------------
| information_schema |
| db_test |
| home |
| mysql |
| performance_schema |
| sys |
--------------------
6 rows in set (0.00 sec)2逻辑备份
[rootlocalhost ~]# /usr/bin/mysqldump -uroot -p --routines --set-gtid-purgedOFF --databases db_test home mysql /root/all-database-20240319.sql
Enter password: 3平滑优雅的停止数据库
master
mysql select version();
------------
| version() |
------------
| 5.7.42-log |
------------
1 row in set (0.00 sec)mysql show variables like innodb_fast_shutdown;
-----------------------------
| Variable_name | Value |
-----------------------------
| innodb_fast_shutdown | 1 |
-----------------------------
1 row in set (0.00 sec)mysql set global innodb_fast_shutdown0;
Query OK, 0 rows affected (0.00 sec)mysql shutdown;
Query OK, 0 rows affected (0.00 sec)mysql exit
Bye slave1
mysql select version();
-----------
| version() |
-----------
| 5.7.42 |
-----------
1 row in set (0.00 sec)mysql show variables like innodb_fast_shutdown;
-----------------------------
| Variable_name | Value |
-----------------------------
| innodb_fast_shutdown | 1 |
-----------------------------
1 row in set (0.01 sec)mysql set global innodb_fast_shutdown0;
Query OK, 0 rows affected (0.00 sec)mysql shutdown;
Query OK, 0 rows affected (0.00 sec)mysql exit
Byeslave2
mysql select version();
-----------
| version() |
-----------
| 5.7.42 |
-----------
1 row in set (0.00 sec)mysql show variables like innodb_fast_shutdown;
-----------------------------
| Variable_name | Value |
-----------------------------
| innodb_fast_shutdown | 1 |
-----------------------------
1 row in set (0.01 sec)mysql set global innodb_fast_shutdown0;
Query OK, 0 rows affected (0.00 sec)mysql shutdown;
Query OK, 0 rows affected (0.01 sec)mysql exit
Bye4查看进程
master
[rootlocalhost ~]# ps -ef | grep mysqlslave1
[rootlocalhost ~]# ps -ef | grep mysqlslave2
[rootlocalhost ~]# ps -ef | grep mysql4.备份MySQL 数据目录、安装目录、配置文件
1确认数据库状态为关闭状态
master
[rootlocalhost ~]# systemctl status mysqldslave1
[rootlocalhost ~]# systemctl status mysqldslave2
[rootlocalhost ~]# systemctl status mysqld(2) 数据目录备份
master
[rootlocalhost mysql]# cp -r /var/lib/mysql /var/lib/mysql_bak_date %F[rootlocalhost lib]# ls | grep mysqlslave1
[rootlocalhost mysql]# cp -r /var/lib/mysql /var/lib/mysql_bak_date %F[rootlocalhost lib]# ls | grep mysqlslave2
[rootlocalhost mysql]# cp -r /var/lib/mysql /var/lib/mysql_bak_date %F[rootlocalhost lib]# ls | grep mysql3配置文件备份
master
[rootlocalhost ~]# cp /etc/my.cnf /etc/my.cnf_date %F[rootlocalhost etc]# ls | grep my.cnfslave1
[rootlocalhost ~]# cp /etc/my.cnf /etc/my.cnf_date %F[rootlocalhost etc]# ls | grep my.cnfslave2
[rootlocalhost ~]# cp /etc/my.cnf /etc/my.cnf_date %F[rootlocalhost etc]# ls | grep my.cnf5.MySQL 升级
(1) 查询
https://downloads.mysql.com/archives/community/ 版本 2下载
wget https://downloads.mysql.com/archives/get/p/23/file/mysql-8.2.0-linux-glibc2.17-x86_64.tar.xz 传送至slave1、slave2
[rootlocalhost ~]# rsync -aXSH --delete mysql-8.2.0-linux-glibc2.17-x86_64.tar.xz 192.168.204.11:~[rootlocalhost ~]# rsync -aXSH --delete mysql-8.2.0-linux-glibc2.17-x86_64.tar.xz 192.168.204.12:~3解压
master
[rootlocalhost ~]# tar -xf mysql-8.2.0-linux-glibc2.17-x86_64.tar.xzslave1
[rootlocalhost ~]# tar -xf mysql-8.2.0-linux-glibc2.17-x86_64.tar.xzslave2
[rootlocalhost ~]# tar -xf mysql-8.2.0-linux-glibc2.17-x86_64.tar.xz4文件夹重命名为mysql8
master
[rootlocalhost ~]# mv mysql-8.2.0-linux-glibc2.17-x86_64 mysql8slave1
[rootlocalhost ~]# mv mysql-8.2.0-linux-glibc2.17-x86_64 mysql8slave2
[rootlocalhost ~]# mv mysql-8.2.0-linux-glibc2.17-x86_64 mysql8(5)更改文件夹所属
master
[rootlocalhost local]# chown -Rf mysql:mysql /usr/local/mysql8 slave1
[rootlocalhost local]# chown -Rf mysql:mysql /usr/local/mysql8 slave2
[rootlocalhost local]# chown -Rf mysql:mysql /usr/local/mysql8 (6) 修改配置文件
对原有5.7的配置文件新增如下配置mysql8.0的配置项
log_replica_updates ONbinlog_expire_logs_seconds259200#for8.0
sql_mode STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
character_set_serverutf8mb4
collation-serverutf8mb4_0900_ai_ci
basedir /usr/local/mysql8
default_authentication_plugin caching_sha2_passworddefault-storage-engineINNODB主要注意sql_mode、basedir、密码认证插件及字符集设置其他参数最好还是按照原5.7的来不需要做调整。 7执行升级程序
会一直卡住
[rootlocalhost ~]# /usr/local/mysql8/bin/mysqld_safe --defaults-file/etc/my.cnf --usermysql --upgradeFORCE
2024-03-19T16:02:35.733625Z mysqld_safe Logging to /var/log/mysqld.log.
2024-03-19T16:02:35.772820Z mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql新开一个窗口可观察下错误日志
[rootlocalhost ~]# tailf -n 12 /var/log/mysqld.log
2024-03-19T16:02:35.790837Z 0 [System] [MY-015015] [Server] MySQL Server - start.
2024-03-19T16:02:36.130287Z 0 [Warning] [MY-011070] [Server] binlog_format is deprecated and will be removed in a future release.
2024-03-19T16:02:36.130707Z 0 [System] [MY-010116] [Server] /usr/local/mysql8/bin/mysqld (mysqld 8.2.0) starting as process 8423
2024-03-19T16:02:36.168393Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2024-03-19T16:02:36.984776Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2024-03-19T16:02:37.308974Z 0 [Warning] [MY-013129] [Server] A message intended for a client cannot be sent there as no client-session is attached. Therefore, were sending the information to the error-log instead: MY-001287 - validate password plugin is deprecated and will be removed in a future release. Please use validate_password component instead
2024-03-19T16:02:37.351393Z 4 [System] [MY-013381] [Server] Server upgrade from 80200 to 80200 started.
2024-03-19T16:02:44.272950Z 4 [System] [MY-013381] [Server] Server upgrade from 80200 to 80200 completed.
2024-03-19T16:02:44.596670Z 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed.
2024-03-19T16:02:44.596752Z 0 [System] [MY-013602] [Server] Channel mysql_main configured to support TLS. Encrypted connections are now supported for this channel.
2024-03-19T16:02:44.642943Z 0 [System] [MY-010931] [Server] /usr/local/mysql8/bin/mysqld: ready for connections. Version: 8.2.0 socket: /var/lib/mysql/mysql.sock port: 3306 MySQL Community Server - GPL.
2024-03-19T16:02:44.643653Z 0 [System] [MY-011323] [Server] X Plugin ready for connections. Bind-address: :: port: 33060, socket: /tmp/mysqlx.sock8登录数据库
[rootlocalhost ~]# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 10
Server version: 8.2.0 MySQL Community Server - GPLCopyright (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 select version();
-----------
| version() |
-----------
| 8.2.0 |
-----------
1 row in set (0.00 sec)mysql 9申明变量
[rootlocalhost ~]# vim /etc/profileexport PATH/usr/local/mysql8/bin:$PATH 10更新
[rootlocalhost local]# source /etc/profile11查看运行位置
[rootlocalhost local]# which mysql
/usr/local/mysql8/bin/mysql12查看版本
[rootlocalhost local]# mysql -V
mysql Ver 8.2.0 for Linux on x86_64 (MySQL Community Server - GPL)6.master节点 使用systemd管理mysql8
(1) 查看进程
[rootlocalhost ~]# ps -ef | grep mysql
root 8129 2857 0 00:02 pts/1 00:00:00 /bin/sh /usr/local/mysql8/bin/mysqld_safe --defaults-file/etc/my.cnf --usermysql --upgradeFORCE
mysql 8423 8129 2 00:02 pts/1 00:00:16 /usr/local/mysql8/bin/mysqld --defaults-file/etc/my.cnf --basedir/usr/local/mysql8 --datadir/var/lib/mysql --plugin-dir/usr/local/mysql8/lib/plugin --usermysql --upgradeFORCE --log-error/var/log/mysqld.log --pid-file/var/run/mysqld/mysqld.pid --socket/var/lib/mysql/mysql.sock
root 8934 8715 0 00:16 pts/0 00:00:00 grep --colorauto mysql2awk查询
[rootlocalhost ~]# ps -ef | grep mysql | awk {print $2}
8129
8423
89313停止mysqld_safe进程
[rootlocalhost ~]# kill -9 ps -ef | grep mysql | awk {print $2}观察之前的命令 确认没有mysql进程 3修改systemd配置
修改原先的ExecStart中basedir的路径改为mysql8 的路径。
如果不存在就新建一个。
[rootlocalhost ~]# vim /etc/systemd/system/mysqld.service[Unit]DescriptionMySQL ServerDocumentationman:mysqldDocumentationhttp://dev.mysql.com/doc/refman/en/using-systemd.htmlAfternetwork.targetAftersyslog.target[Install]WantedBymulti-user.target[Service]UsermysqlGroupmysqlExecStart/usr/local/mysql8/bin/mysqld --defaults-file/etc/my.cnfLimitNOFILE 655354配置mysql8开机自启
重新加载
[rootlocalhost ~]# systemctl daemon-reload开机启动
[rootlocalhost ~]# systemctl enable mysqld启动服务
[rootlocalhost ~]# systemctl start mysqld查看进程
[rootlocalhost ~]# ps -ef | grep mysql
mysql 9062 1 9 00:24 ? 00:00:01 /usr/local/mysql8/bin/mysqld --defaults-file/etc/my.cnf
root 9110 8715 0 00:24 pts/0 00:00:00 grep --colorauto mysql5登录数据库
数据已恢复。
[rootlocalhost ~]# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.2.0 MySQL Community Server - GPLCopyright (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 select version();
-----------
| version() |
-----------
| 8.2.0 |
-----------
1 row in set (0.00 sec)mysql show databases;
--------------------
| Database |
--------------------
| db_test |
| home |
| information_schema |
| mysql |
| performance_schema |
| sys |
--------------------
6 rows in set (0.00 sec)mysql exit
Bye7. slave1 节点升级
1修改配置文件
[rootlocalhost local]# vim /etc/my.cnf(2) 执行升级程序
会一直卡住
[rootlocalhost mysql]# /usr/local/mysql8/bin/mysqld_safe --defaults-file/etc/my.cnf --usermysql --upgradeFORCE
2024-03-19T17:47:05.177779Z mysqld_safe Logging to /var/log/mysqld.log.
2024-03-19T17:47:05.217935Z mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql新开一个窗口可观察下错误日志
[rootlocalhost ~]# tailf -n 16 /var/log/mysqld.log
2024-03-19T17:47:05.258615Z 0 [System] [MY-015015] [Server] MySQL Server - start.
2024-03-19T17:47:05.603689Z 0 [System] [MY-010116] [Server] /usr/local/mysql8/bin/mysqld (mysqld 8.2.0) starting as process 10120
2024-03-19T17:47:05.663382Z 1 [System] [MY-011012] [Server] Starting upgrade of data directory.
2024-03-19T17:47:05.663627Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2024-03-19T17:47:06.921126Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2024-03-19T17:47:07.578164Z 0 [Warning] [MY-013129] [Server] A message intended for a client cannot be sent there as no client-session is attached. Therefore, were sending the information to the error-log instead: MY-001287 - validate password plugin is deprecated and will be removed in a future release. Please use validate_password component instead
2024-03-19T17:47:08.578589Z 2 [System] [MY-011003] [Server] Finished populating Data Dictionary tables with data.
2024-03-19T17:47:09.542512Z 5 [System] [MY-013381] [Server] Server upgrade from 50700 to 80200 started.
2024-03-19T17:47:15.410404Z 5 [System] [MY-013381] [Server] Server upgrade from 50700 to 80200 completed.
2024-03-19T17:47:15.583737Z 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed.
2024-03-19T17:47:15.583828Z 0 [System] [MY-013602] [Server] Channel mysql_main configured to support TLS. Encrypted connections are now supported for this channel.
2024-03-19T17:47:15.628136Z 0 [Warning] [MY-010539] [Repl] Recovery from source pos 1372 and file mysql-bin.000007 for channel . Previous relay log pos and relay log file had been set to 950, ./relay-log-bin.000047 respectively.
2024-03-19T17:47:15.632722Z 9 [Warning] [MY-010897] [Repl] Storing MySQL user name or password information in the connection metadata repository is not secure and is therefore not recommended. Please consider using the USER and PASSWORD connection options for START REPLICA; see the START REPLICA Syntax in the MySQL Manual for more information.
2024-03-19T17:47:15.641476Z 0 [System] [MY-010931] [Server] /usr/local/mysql8/bin/mysqld: ready for connections. Version: 8.2.0 socket: /var/lib/mysql/mysql.sock port: 3306 MySQL Community Server - GPL.
2024-03-19T17:47:15.641667Z 0 [System] [MY-011323] [Server] X Plugin ready for connections. Bind-address: :: port: 33060, socket: /tmp/mysqlx.sock
2024-03-19T17:47:15.831305Z 9 [System] [MY-014001] [Repl] Replica receiver thread for channel : connected to source myslave192.168.204.10:3306 with server_uuidc8246fd9-1c99-11ee-af46-000c29747129, server_id1. Starting replication from file mysql-bin.000007, position 1372. 3登录数据库
[rootlocalhost ~]# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 10
Server version: 8.2.0 MySQL Community Server - GPLCopyright (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 select version();
-----------
| version() |
-----------
| 8.2.0 |
-----------
1 row in set (0.00 sec)mysql 4申明变量
[rootlocalhost ~]# vim /etc/profileexport PATH/usr/local/mysql8/bin:$PATH 10更新
[rootlocalhost local]# source /etc/profile11查看运行位置
[rootlocalhost local]# which mysql
/usr/local/mysql8/bin/mysql12查看版本
[rootlocalhost local]# mysql -V
mysql Ver 8.2.0 for Linux on x86_64 (MySQL Community Server - GPL)13使用systemd管理mysql8
关闭进程 观察 启动 14查看 8. slave2 节点升级 1修改配置文件
[rootlocalhost local]# vim /etc/my.cnf(2) 执行升级程序
会一直卡住
[rootlocalhost mysql]# /usr/local/mysql8/bin/mysqld_safe --defaults-file/etc/my.cnf --usermysql --upgradeFORCE
2024-03-19T18:08:36.722141Z mysqld_safe Logging to /var/log/mysqld.log.
2024-03-19T18:08:36.764305Z mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql 新开一个窗口可观察下错误日志
[rootlocalhost ~]# tailf -n 16 /var/log/mysqld.log
2024-03-19T18:08:36.830085Z 0 [System] [MY-015015] [Server] MySQL Server - start.
2024-03-19T18:08:38.120840Z 0 [System] [MY-010116] [Server] /usr/local/mysql8/bin/mysqld (mysqld 8.2.0) starting as process 7284
2024-03-19T18:08:38.205593Z 1 [System] [MY-011012] [Server] Starting upgrade of data directory.
2024-03-19T18:08:38.205726Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2024-03-19T18:08:39.349587Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2024-03-19T18:08:39.754272Z 0 [Warning] [MY-013129] [Server] A message intended for a client cannot be sent there as no client-session is attached. Therefore, were sending the information to the error-log instead: MY-001287 - validate password plugin is deprecated and will be removed in a future release. Please use validate_password component instead
2024-03-19T18:08:40.131905Z 2 [System] [MY-011003] [Server] Finished populating Data Dictionary tables with data.
2024-03-19T18:08:40.914022Z 5 [System] [MY-013381] [Server] Server upgrade from 50700 to 80200 started.
2024-03-19T18:08:46.254503Z 5 [System] [MY-013381] [Server] Server upgrade from 50700 to 80200 completed.
2024-03-19T18:08:46.503504Z 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed.
2024-03-19T18:08:46.503609Z 0 [System] [MY-013602] [Server] Channel mysql_main configured to support TLS. Encrypted connections are now supported for this channel.
2024-03-19T18:08:46.556282Z 0 [Warning] [MY-010539] [Repl] Recovery from source pos 1372 and file mysql-bin.000007 for channel . Previous relay log pos and relay log file had been set to 320, ./relay-log-bin.000025 respectively.
2024-03-19T18:08:46.560468Z 9 [Warning] [MY-010897] [Repl] Storing MySQL user name or password information in the connection metadata repository is not secure and is therefore not recommended. Please consider using the USER and PASSWORD connection options for START REPLICA; see the START REPLICA Syntax in the MySQL Manual for more information.
2024-03-19T18:08:46.568407Z 0 [System] [MY-010931] [Server] /usr/local/mysql8/bin/mysqld: ready for connections. Version: 8.2.0 socket: /var/lib/mysql/mysql.sock port: 3306 MySQL Community Server - GPL.
2024-03-19T18:08:46.578217Z 0 [System] [MY-011323] [Server] X Plugin ready for connections. Bind-address: :: port: 33060, socket: /tmp/mysqlx.sock
2024-03-19T18:08:46.790350Z 9 [System] [MY-014001] [Repl] Replica receiver thread for channel : connected to source myslave192.168.204.10:3306 with server_uuidc8246fd9-1c99-11ee-af46-000c29747129, server_id1. Starting replication from file mysql-bin.000007, position 1372. 3登录数据库
[rootlocalhost ~]# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 10
Server version: 8.2.0 MySQL Community Server - GPLCopyright (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 select version();
-----------
| version() |
-----------
| 8.2.0 |
-----------
1 row in set (0.00 sec)mysql 4申明变量
[rootlocalhost ~]# vim /etc/profileexport PATH/usr/local/mysql8/bin:$PATH 5更新
[rootlocalhost local]# source /etc/profile6查看运行位置
[rootlocalhost local]# which mysql
/usr/local/mysql8/bin/mysql7查看版本
[rootlocalhost local]# mysql -V
mysql Ver 8.2.0 for Linux on x86_64 (MySQL Community Server - GPL)8使用systemd管理mysql8
关闭进程 观察 启动 9查看 9.半同步设置
1加载插件
要加载插件在master源和要半同步的每个副本上使用 INSTALL PLUGIN 语句并根据需要为平台调整 .so 后缀。
master:
INSTALL PLUGIN rpl_semi_sync_source SONAME semisync_source.so; slave1:
INSTALL PLUGIN rpl_semi_sync_replica SONAME semisync_replica.so; slave2:
INSTALL PLUGIN rpl_semi_sync_replica SONAME semisync_replica.so; (3) 验证插件是否安装如报错必须安装 libimf
master
SELECT PLUGIN_NAME, PLUGIN_STATUS FROM INFORMATION_SCHEMA.PLUGINS WHERE PLUGIN_NAME LIKE %semi%; slave1 slave2 4启用插件
master
[rootlocalhost mysql]# vim /etc/my.cnf
……
rpl_semi_sync_source_enabled1
…… slave1
[rootlocalhost mysql]# vim /etc/my.cnf
……
rpl_semi_sync_replica_enabled1
…… slave2
[rootlocalhost mysql]# vim /etc/my.cnf
……
rpl_semi_sync_replica_enabled1
…… (5) 全部节点重启
[rootlocalhost mysql]# systemctl restart mysqld6查看进程
master slave1 slave2 (7)检查半同步复制状态变量的当前值
mysql SHOW VARIABLES LIKE rpl_semi_sync%;
---------------------------------------------------------
| Variable_name | Value |
---------------------------------------------------------
| rpl_semi_sync_source_enabled | ON |
| rpl_semi_sync_source_timeout | 10000 |
| rpl_semi_sync_source_trace_level | 32 |
| rpl_semi_sync_source_wait_for_replica_count | 1 |
| rpl_semi_sync_source_wait_no_replica | ON |
| rpl_semi_sync_source_wait_point | AFTER_SYNC |
---------------------------------------------------------
6 rows in set (0.02 sec)8监视半同步复制的插件状态
Rpl_semi_sync_source_clients 连接到源服务器的半同步副本的数量变为2
mysql SHOW STATUS LIKE Rpl_semi_sync%;
---------------------------------------------------
| Variable_name | Value |
---------------------------------------------------
| Rpl_semi_sync_source_clients | 2 |
| Rpl_semi_sync_source_net_avg_wait_time | 0 |
| Rpl_semi_sync_source_net_wait_time | 0 |
| Rpl_semi_sync_source_net_waits | 0 |
| Rpl_semi_sync_source_no_times | 0 |
| Rpl_semi_sync_source_no_tx | 0 |
| Rpl_semi_sync_source_status | ON |
| Rpl_semi_sync_source_timefunc_failures | 0 |
| Rpl_semi_sync_source_tx_avg_wait_time | 0 |
| Rpl_semi_sync_source_tx_wait_time | 0 |
| Rpl_semi_sync_source_tx_waits | 0 |
| Rpl_semi_sync_source_wait_pos_backtraverse | 0 |
| Rpl_semi_sync_source_wait_sessions | 0 |
| Rpl_semi_sync_source_yes_tx | 0 |
---------------------------------------------------
14 rows in set (0.00 sec) 9查看状态
master
mysql show master status\G
*************************** 1. row ***************************File: mysql-bin.000013Position: 157Binlog_Do_DB: Binlog_Ignore_DB:
Executed_Gtid_Set:
1 row in set, 1 warning (0.00 sec)slave1
mysql show slave status\G
*************************** 1. row ***************************Slave_IO_State: Waiting for source to send eventMaster_Host: 192.168.204.10Master_User: myslaveMaster_Port: 3306Connect_Retry: 60Master_Log_File: mysql-bin.000013Read_Master_Log_Pos: 157Relay_Log_File: relay-log-bin.000059Relay_Log_Pos: 373Relay_Master_Log_File: mysql-bin.000013Slave_IO_Running: YesSlave_SQL_Running: YesReplicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0Last_Error: Skip_Counter: 0Exec_Master_Log_Pos: 157Relay_Log_Space: 750Until_Condition: NoneUntil_Log_File: Until_Log_Pos: 0Master_SSL_Allowed: NoMaster_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: NoLast_IO_Errno: 0Last_IO_Error: Last_SQL_Errno: 0Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 1Master_UUID: c8246fd9-1c99-11ee-af46-000c29747129Master_Info_File: mysql.slave_master_infoSQL_Delay: 0SQL_Remaining_Delay: NULLSlave_SQL_Running_State: Replica has read all relay log; waiting for more updatesMaster_Retry_Count: 86400Master_Bind: Last_IO_Error_Timestamp: Last_SQL_Error_Timestamp: Master_SSL_Crl: Master_SSL_Crlpath: Retrieved_Gtid_Set: Executed_Gtid_Set: Auto_Position: 0Replicate_Rewrite_DB: Channel_Name: Master_TLS_Version: Master_public_key_path: Get_master_public_key: 0Network_Namespace:
1 row in set, 1 warning (0.00 sec)slave2
mysql show slave status\G
*************************** 1. row ***************************Slave_IO_State: Waiting for source to send eventMaster_Host: 192.168.204.10Master_User: myslaveMaster_Port: 3306Connect_Retry: 60Master_Log_File: mysql-bin.000013Read_Master_Log_Pos: 157Relay_Log_File: relay-log-bin.000037Relay_Log_Pos: 373Relay_Master_Log_File: mysql-bin.000013Slave_IO_Running: YesSlave_SQL_Running: YesReplicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0Last_Error: Skip_Counter: 0Exec_Master_Log_Pos: 157Relay_Log_Space: 750Until_Condition: NoneUntil_Log_File: Until_Log_Pos: 0Master_SSL_Allowed: NoMaster_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: NoLast_IO_Errno: 0Last_IO_Error: Last_SQL_Errno: 0Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 1Master_UUID: c8246fd9-1c99-11ee-af46-000c29747129Master_Info_File: mysql.slave_master_infoSQL_Delay: 0SQL_Remaining_Delay: NULLSlave_SQL_Running_State: Replica has read all relay log; waiting for more updatesMaster_Retry_Count: 86400Master_Bind: Last_IO_Error_Timestamp: Last_SQL_Error_Timestamp: Master_SSL_Crl: Master_SSL_Crlpath: Retrieved_Gtid_Set: Executed_Gtid_Set: Auto_Position: 0Replicate_Rewrite_DB: Channel_Name: Master_TLS_Version: Master_public_key_path: Get_master_public_key: 0Network_Namespace:
1 row in set, 1 warning (0.00 sec)(9)创建数据库
master
mysql CREATE DATABASE club;(10) 查看数据库
slave1
mysql show databases;
--------------------
| Database |
--------------------
| club |
| db_test |
| home |
| information_schema |
| mysql |
| performance_schema |
| sys |
--------------------
7 rows in set (0.01 sec)slave2
mysql show databases;
--------------------
| Database |
--------------------
| club |
| db_test |
| home |
| information_schema |
| mysql |
| performance_schema |
| sys |
--------------------
7 rows in set (0.01 sec)二、问题
1.mysqldump备份报错
1报错
mysqldump: Got error: 1044: Access denied for user rootlocalhost to database information_schema when using LOCK TABLES2原因分析
mysqldump 命令执行时需要四种权限分别是select,show view,trigger,lock table。但是因为没有lock table的权限导致上述错误发生。
3解决方法
在mysqldump命令之后添加--single-transaction 即可。
执行
[rootlocalhost ~]# /usr/bin/mysqldump --single-transaction -uroot -p --routines --set-gtid-purgedOFF --databases information_schema db_test home mysql performance_schema sys /root/all-database-20240319.sql
Enter password:
mysqldump: Couldnt execute SELECT /*!40001 SQL_NO_CACHE */ * FROM GLOBAL_STATUS: The INFORMATION_SCHEMA.GLOBAL_STATUS feature is disabled; see the documentation for show_compatibility_56 (3167)上面出现再次报错因为MySQL 其内置的四个数据库mysql、information_schema、sys和performance_schema
1mysql数据库
mysql数据库是存储MySQL服务器的系统和用户权限信息的地方。它包含了用户、权限、角色等相关信息。这个数据库是非常重要的因为它控制着MySQL服务器的访问和操作权限。2information_schema数据库
information_schema数据库是一个元数据信息存储库它包含了关于MySQL服务器中所有数据库、表、列、索引等对象的信息。通过查询information_schema数据库可以获取关于数据库结构和元数据的详细信息。3sys数据库
sys数据库是MySQL 8.0版本引入的一个新特性它提供了一组视图和存储过程用于简化和改进对MySQL服务器的监控和性能分析。sys数据库中的视图可以帮助用户更方便地获取和分析MySQL服务器的性能指标和状态信息。4performance_schema数据库
performance_schema数据库也是MySQL 5.5版本引入的一个新特性它提供了一组性能监控相关的表和视图用于收集和展示MySQL服务器的性能数据。通过performance_schema数据库可以监控和分析MySQL服务器的查询性能、锁等待、I/O操作等方面的信息。
需要备份这些内置数据库取决于你的具体需求和情况。一般来说mysql数据库是非常重要的因为它包含了用户和权限信息建议定期备份。information_schema、sys和performance_schema数据库通常不需要备份因为它们是动态生成的可以通过查询获取最新的信息。
[rootlocalhost ~]# /usr/bin/mysqldump -uroot -p --routines --set-gtid-purgedOFF --databases db_test home mysql /root/all-database-20240319.sql
Enter password: 2.InnoDB 有哪些关闭模式。
1模式
如果值为 0InnoDB 会在关闭前进行缓慢关闭、完全清除和更改缓冲区合并。
set global innodb_fast_shutdown0;
如果值为 1默认值InnoDB 会在关闭时跳过这些操作这个过程称为快速关闭。
set global innodb_fast_shutdown1;
如果值为 2InnoDB 刷新其日志并冷关机就好像 MySQL 崩溃了没有提交的事务丢失但崩溃恢复操作使下一次启动需要更长的时间。 在仍然缓冲大量数据的极端情况下缓慢关闭可能需要几分钟甚至几小时。
set global innodb_fast_shutdown2; 3.master节点执行升级程序报错
(1) 报错 2原因分析
查看日志
[rootlocalhost ~]# tailf -n 20 /var/log/mysqld.log
2024-03-19T15:16:23.939891Z 0 [System] [MY-015015] [Server] MySQL Server - start.
2024-03-19T15:16:24.362405Z 0 [Warning] [MY-011070] [Server] binlog_format is deprecated and will be removed in a future release.
2024-03-19T15:16:24.362496Z 0 [Warning] [MY-011068] [Server] The syntax log_slave_updates is deprecated and will be removed in a future release. Please use log_replica_updates instead.
2024-03-19T15:16:24.362580Z 0 [Warning] [MY-011070] [Server] Disabling symbolic links using --skip-symbolic-links (or equivalent) is the default. Consider not using this option as it is deprecated and will be removed in a future release.
2024-03-19T15:16:24.362600Z 0 [Warning] [MY-011068] [Server] The syntax --ssloff is deprecated and will be removed in a future release. Please use --tls-version instead.
2024-03-19T15:16:24.362884Z 0 [Warning] [MY-010918] [Server] default_authentication_plugin is deprecated and will be removed in a future release. Please use authentication_policy instead.
2024-03-19T15:16:24.362927Z 0 [System] [MY-010116] [Server] /usr/local/mysql8/bin/mysqld (mysqld 8.2.0) starting as process 7067
2024-03-19T15:16:24.370604Z 0 [Warning] [MY-013242] [Server] --character-set-server: utf8 is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous.
2024-03-19T15:16:24.370621Z 0 [Warning] [MY-013244] [Server] --collation-server: utf8mb3_general_ci is a collation of the deprecated character set UTF8MB3. Please consider using UTF8MB4 with an appropriate collation instead.
2024-03-19T15:16:24.393063Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2024-03-19T15:16:25.071261Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2024-03-19T15:16:25.345892Z 0 [Warning] [MY-010918] [Repl] rpl_semi_sync_master is deprecated and will be removed in a future release. Please use rpl_semi_sync_source instead.
2024-03-19T15:16:25.346026Z 0 [Warning] [MY-013129] [Server] A message intended for a client cannot be sent there as no client-session is attached. Therefore, were sending the information to the error-log instead: MY-001287 - validate password plugin is deprecated and will be removed in a future release. Please use validate_password component instead
2024-03-19T15:16:25.382686Z 4 [System] [MY-013381] [Server] Server upgrade from 80200 to 80200 started.
2024-03-19T15:16:32.072259Z 4 [System] [MY-013381] [Server] Server upgrade from 80200 to 80200 completed.
2024-03-19T15:16:32.447111Z 0 [ERROR] [MY-000067] [Server] unknown variable expire_logs_days7.
2024-03-19T15:16:32.448921Z 0 [ERROR] [MY-010119] [Server] Aborting
2024-03-19T15:16:34.293524Z 0 [Warning] [MY-011068] [Server] The syntax validate password plugin is deprecated and will be removed in a future release. Please use validate_password component instead.
2024-03-19T15:16:35.317547Z 0 [System] [MY-010910] [Server] /usr/local/mysql8/bin/mysqld: Shutdown complete (mysqld 8.2.0) MySQL Community Server - GPL.
2024-03-19T15:16:35.318861Z 0 [System] [MY-015016] [Server] MySQL Server - end.从 MySQL 8.0.26 开始提供了实现半同步复制的新版本插件一个用于源服务器一个用于副本服务器。新插件在系统变量和状态变量中将术语“master”和“slave”替换为“source”和“replica”您可以安装这些版本而不是旧版本。您不能在一个实例上同时安装新旧版本的相关插件。如果你使用新版本的插件新的系统变量和状态变量可用旧的不可用。如果您使用旧版本的插件旧的系统变量和状态变量可用但新的不可用。
源服务器旧术语rpl_semi_sync_master 插件semisync_master.so 或 semisync_master.dll 库源服务器新术语来自 MySQL 8.0.26rpl_semi_sync_source 插件semisync_source.so 或 semisync_source.dll 库副本旧术语rpl_semi_sync_slave 插件semisync_slave.so 或 semisync_slave.dll 库副本新术语来自 MySQL 8.0.26rpl_semi_sync_replica 插件semisync_replica.so 或 semisync_replica.dll 库3解决方法
注释半同步相关配置 注释自动清除日志时间 添加
binlog_expire_logs_seconds259200修改密码插件
default_authentication_plugincaching_sha2_password 修改字符集
# 指定编码 utf8mb4
character-set-serverutf8mb4# utf8mb4的排序规则
collation-serverutf8mb4_0900_ai_ci 注释SSL 注释symbolic-links 注释 开启
log_replica_updates ON 4. slave 节点执行升级程序报错
1报错 2原因分析
查看日志
[rootlocalhost lib]# tailf -n 30 /var/log/mysqld.log
2024-03-19T17:45:33.170538Z 0 [System] [MY-010116] [Server] /usr/local/mysql8/bin/mysqld (mysqld 8.2.0) starting as process 9819
2024-03-19T17:45:33.245991Z 1 [System] [MY-011012] [Server] Starting upgrade of data directory.
2024-03-19T17:45:33.246104Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2024-03-19T17:45:33.261816Z 1 [ERROR] [MY-012209] [InnoDB] Multiple files found for the same tablespace ID:
2024-03-19T17:45:33.261882Z 1 [ERROR] [MY-012202] [InnoDB] Tablespace ID: 2 [mysql/mysql/plugin.ibd, mysql/plugin.ibd]
2024-03-19T17:45:33.261909Z 1 [ERROR] [MY-012202] [InnoDB] Tablespace ID: 3 [mysql/mysql/servers.ibd, mysql/servers.ibd]
2024-03-19T17:45:33.261927Z 1 [ERROR] [MY-012202] [InnoDB] Tablespace ID: 4 [mysql/help_topic.ibd, mysql/mysql/help_topic.ibd]
2024-03-19T17:45:33.261972Z 1 [ERROR] [MY-012202] [InnoDB] Tablespace ID: 5 [mysql/help_category.ibd, mysql/mysql/help_category.ibd]
2024-03-19T17:45:33.261993Z 1 [ERROR] [MY-012202] [InnoDB] Tablespace ID: 6 [mysql/help_relation.ibd, mysql/mysql/help_relation.ibd]
2024-03-19T17:45:33.262009Z 1 [ERROR] [MY-012202] [InnoDB] Tablespace ID: 7 [mysql/help_keyword.ibd, mysql/mysql/help_keyword.ibd]
2024-03-19T17:45:33.262025Z 1 [ERROR] [MY-012202] [InnoDB] Tablespace ID: 8 [mysql/mysql/time_zone_name.ibd, mysql/time_zone_name.ibd]
2024-03-19T17:45:33.262041Z 1 [ERROR] [MY-012202] [InnoDB] Tablespace ID: 9 [mysql/mysql/time_zone.ibd, mysql/time_zone.ibd]
2024-03-19T17:45:33.262057Z 1 [ERROR] [MY-012202] [InnoDB] Tablespace ID: 10 [mysql/mysql/time_zone_transition.ibd, mysql/time_zone_transition.ibd]
2024-03-19T17:45:33.262073Z 1 [ERROR] [MY-012202] [InnoDB] Tablespace ID: 11 [mysql/mysql/time_zone_transition_type.ibd, mysql/time_zone_transition_type.ibd]
2024-03-19T17:45:33.262089Z 1 [ERROR] [MY-012202] [InnoDB] Tablespace ID: 12 [mysql/mysql/time_zone_leap_second.ibd, mysql/time_zone_leap_second.ibd]
2024-03-19T17:45:33.262105Z 1 [ERROR] [MY-012202] [InnoDB] Tablespace ID: 13 [mysql/innodb_table_stats.ibd, mysql/mysql/innodb_table_stats.ibd]
2024-03-19T17:45:33.262120Z 1 [ERROR] [MY-012202] [InnoDB] Tablespace ID: 14 [mysql/innodb_index_stats.ibd, mysql/mysql/innodb_index_stats.ibd]
2024-03-19T17:45:33.262135Z 1 [ERROR] [MY-012202] [InnoDB] Tablespace ID: 15 [mysql/mysql/slave_relay_log_info.ibd, mysql/slave_relay_log_info.ibd]
2024-03-19T17:45:33.262150Z 1 [ERROR] [MY-012202] [InnoDB] Tablespace ID: 16 [mysql/mysql/slave_master_info.ibd, mysql/slave_master_info.ibd]
2024-03-19T17:45:33.262165Z 1 [ERROR] [MY-012202] [InnoDB] Tablespace ID: 17 [mysql/mysql/slave_worker_info.ibd, mysql/slave_worker_info.ibd]
2024-03-19T17:45:33.262195Z 1 [ERROR] [MY-012202] [InnoDB] Tablespace ID: 18 [mysql/gtid_executed.ibd, mysql/mysql/gtid_executed.ibd]
2024-03-19T17:45:33.262216Z 1 [ERROR] [MY-012202] [InnoDB] Tablespace ID: 19 [mysql/mysql/server_cost.ibd, mysql/server_cost.ibd]
2024-03-19T17:45:33.262271Z 1 [ERROR] [MY-012202] [InnoDB] Tablespace ID: 20 [mysql/engine_cost.ibd, mysql/mysql/engine_cost.ibd]
2024-03-19T17:45:33.262292Z 1 [ERROR] [MY-012202] [InnoDB] Tablespace ID: 21 [mysql/sys/sys_config.ibd, sys/sys_config.ibd]
2024-03-19T17:45:33.262325Z 1 [ERROR] [MY-012930] [InnoDB] Plugin initialization aborted with error Failed, retry may succeed.
2024-03-19T17:45:33.262409Z 1 [ERROR] [MY-011013] [Server] Failed to initialize DD Storage Engine.
2024-03-19T17:45:33.262697Z 0 [ERROR] [MY-010020] [Server] Data Dictionary initialization failed.
2024-03-19T17:45:33.262726Z 0 [ERROR] [MY-010119] [Server] Aborting
2024-03-19T17:45:33.263664Z 0 [System] [MY-010910] [Server] /usr/local/mysql8/bin/mysqld: Shutdown complete (mysqld 8.2.0) MySQL Community Server - GPL.
2024-03-19T17:45:33.265091Z 0 [System] [MY-015016] [Server] MySQL Server - end.3解决方法
删除重复的库。 成功 5.监视半同步复制的插件状态报错 1报错
Rpl_semi_sync_source_clients 连接到源服务器的半同步副本的数量 为0
mysql SHOW STATUS LIKE Rpl_semi_sync%;
---------------------------------------------------
| Variable_name | Value |
---------------------------------------------------
| Rpl_semi_sync_source_clients | 0 |
| Rpl_semi_sync_source_net_avg_wait_time | 0 |
| Rpl_semi_sync_source_net_wait_time | 0 |
| Rpl_semi_sync_source_net_waits | 0 |
| Rpl_semi_sync_source_no_times | 0 |
| Rpl_semi_sync_source_no_tx | 0 |
| Rpl_semi_sync_source_status | ON |
| Rpl_semi_sync_source_timefunc_failures | 0 |
| Rpl_semi_sync_source_tx_avg_wait_time | 0 |
| Rpl_semi_sync_source_tx_wait_time | 0 |
| Rpl_semi_sync_source_tx_waits | 0 |
| Rpl_semi_sync_source_wait_pos_backtraverse | 0 |
| Rpl_semi_sync_source_wait_sessions | 0 |
| Rpl_semi_sync_source_yes_tx | 0 |
---------------------------------------------------
14 rows in set (0.00 sec)2原因分析
配置文件错误。
测试发现从节点出现未知变量
2024-03-19T18:59:42.762527Z 0 [ERROR] [MY-000067] [Server] unknown variable rpl_semi_sync_source_enabled1.3解决方法
修改slave1 与 slave2 配置文件。
修改前 修改后 成功Rpl_semi_sync_source_clients 连接到源服务器的半同步副本的数量变为2
