续：当有数据时添加slave2

【示例】

另启一台虚拟机，作为mysql3.

新的虚拟机没有mysql软件包，如何才能快速部署？通过mysql1.

mysql1：

[root@mysql1 ~]# rsync -al /usr/local/mysql/ root@172.25.254.166:/usr/local/mysql
The authenticity of host '172.25.254.166 (172.25.254.166)' can't be established.
ECDSA key fingerprint is SHA256:1m5IRRo+zVA9sKXK9QYE0jYLtAv1N3k516SZFFwGJu0.
ECDSA key fingerprint is MD5:45:ca:cf:c6:02:53:c2:b1:1e:ff:92:da:25:00:ea:19.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added '172.25.254.166' (ECDSA) to the list of known hosts.
root@172.25.254.166's password:

mysql3：

[root@mysql3 ~]# useradd -s /sbin/nologin -M mysql
[root@mysql3 ~]# mkdir -p /data/mysql
[root@mysql3 ~]#
[root@mysql3 ~]# chown mysql.mysql /data/mysql/

[root@mysql3 local]# cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld
[root@mysql3 local]#

##环境变量
[root@mysql3 local]# vim ~/.bash_profile
[root@mysql3 ~]#
[root@mysql3 ~]# cat ~/.bash_profile
# .bash_profile

# Get the aliases and functions
if [ -f ~/.bashrc ]; then
. ~/.bashrc
fi

# User specific environment and startup programs

PATH=$PATH:$HOME/bin:/usr/local/mysql/bin

export PATH

[root@mysql3 ~]# vim /etc/my.cnf
[root@mysql3 ~]#
[root@mysql3 ~]# cat /etc/my.cnf
[mysqld]
datadir=/data/mysql
socket=/data/mysql/mysql.sock
symbolic-links=0
server_id=3
#super_read_only=on

#初始化mysql

[root@mysql3 ~]# mysqld --user=mysql --initialize
2024-08-26T18:06:34.996447Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2024-08-26T18:06:35.725476Z 0 [Warning] InnoDB: New log files created, LSN=45790
2024-08-26T18:06:35.866173Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2024-08-26T18:06:35.933982Z 0 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: eeeb0852-63d5-11ef-8e1b-000c29a7a897.
2024-08-26T18:06:35.937678Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2024-08-26T18:06:36.672776Z 0 [Warning] A deprecated TLS version TLSv1 is enabled. Please use TLSv1.2 or higher.
2024-08-26T18:06:36.672820Z 0 [Warning] A deprecated TLS version TLSv1.1 is enabled. Please use TLSv1.2 or higher.
2024-08-26T18:06:36.673904Z 0 [Warning] CA certificate ca.pem is self signed.
2024-08-26T18:06:36.815738Z 1 [Note] A temporary password is generated for root@localhost: Z%E(/tYtI7GH
[root@mysql3 ~]#
[root@mysql3 ~]# vim passwd.txt
[root@mysql3 ~]#
[root@mysql3 ~]# cat passwd.txt
Z%E(/tYtI7GH

##能查看到则成功
[root@mysql3 ~]# ls /data/mysql/
auto.cnf client-cert.pem ibdata1 mysql public_key.pem sys
ca-key.pem client-key.pem ib_logfile0 performance_schema server-cert.pem
ca.pem ib_buffer_pool ib_logfile1 private_key.pem server-key.pem

#启动

[root@mysql3 ~]# /etc/init.d/mysqld start
Starting MySQL.Logging to '/data/mysql/mysql3.err'.
. SUCCESS!
[root@mysql3 ~]#

##

[root@mysql3 ~]# mysql_secure_installation

Securing the MySQL server deployment.

Enter password for user root:

The existing password for the user account root has expired. Please set a new password.

New password:

Re-enter new password:

VALIDATE PASSWORD PLUGIN can be used to test passwords
and improve security. It checks the strength of password
and allows the users to set only those passwords which are
secure enough. Would you like to setup VALIDATE PASSWORD plugin?

Press y|Y for Yes, any other key for No: n
Using existing password for root.
Change the password for root ? ((Press y|Y for Yes, any other key for No) : n

... skipping.
By default, a MySQL installation has an anonymous user,
allowing anyone to log into MySQL without having to have
a user account created for them. This is intended only for
testing, and to make the installation go a bit smoother.
You should remove them before moving into a production
environment.

Remove anonymous users? (Press y|Y for Yes, any other key for No) : y
Success.

Normally, root should only be allowed to connect from
'localhost'. This ensures that someone cannot guess at
the root password from the network.

Disallow root login remotely? (Press y|Y for Yes, any other key for No) : y
Success.

By default, MySQL comes with a database named 'test' that
anyone can access. This is also intended only for testing,
and should be removed before moving into a production
environment.

Remove test database and access to it? (Press y|Y for Yes, any other key for No) : y
- Dropping test database...
Success.

- Removing privileges on test database...
Success.

Reloading the privilege tables will ensure that all changes
made so far will take effect immediately.

Reload privilege tables now? (Press y|Y for Yes, any other key for No) : y
Success.

All done!

至此，部署好了mysql！！

拉平数据：

在已有主从的情况下，再加一个从，如果直接设定mysql3（slave2）为mysql1（master）的从，mysql1（master）现有的数据不能同步到slave2（mysql3）。要使得slave2（mysql3），能同步到master（mysql1）已有的数据！！必须做拉平数据。

1、对master（mysql1）必须进行锁库、锁表：保证数据库不能写，保证备份前后的数据一致（就是不能一边在备份，一边还在往数据库里写……）

##主节点master数据备份：

[root@mysql1 ~]# mysqldump -uroot -p folian > folian.sql
Enter password:
[root@mysql1 ~]# cat folian.sql
-- MySQL dump 10.13 Distrib 5.7.44, for Linux (x86_64)
--
-- Host: localhost Database: folian
-- ------------------------------------------------------
-- Server version   5.7.44-log

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

--
-- Table structure for table `userlist`
--

DROP TABLE IF EXISTS `userlist`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `userlist` (
`username` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL,
`password` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `userlist`
--

LOCK TABLES `userlist` WRITE; #锁了一个叫userlist的库，还得指定表
/*!40000 ALTER TABLE `userlist` DISABLE KEYS */;
INSERT INTO `userlist` VALUES ('folian','123');
/*!40000 ALTER TABLE `userlist` ENABLE KEYS */;
UNLOCK TABLES;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

-- Dump completed on 2024-08-27 2:42:23
[root@mysql1 ~]# scp folian.sql root@172.25.254.166:/mnt/
root@172.25.254.166's password:
folian.sql

[root@mysql3 ~]# cd /mnt/
[root@mysql3 mnt]# ls
folian.sql
mysql3上有了folian.sql文件，如何恢复回去？？

##注意恢复的时候没有库，所以需要建库

[root@mysql3 ~]# mysql -uroot -predhat -e "CREATE DATABASE folian;"
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@mysql3 ~]# mysql -uroot -predhat folian < folian.sql
-bash: folian.sql: No such file or directory
[root@mysql3 ~]#
[root@mysql3 ~]# cd /mnt/
[root@mysql3 mnt]# mysql -uroot -predhat folian < folian.sql ##指定库，把folian.sql导进去
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@mysql3 mnt]#
以上即=拉平数据》》

@

@

[root@mysql3 ~]# mysql -uroot -predhat
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 11
Server version: 5.7.44 Source distribution

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> CHANGE MASTER TO MASTER_HOST='172.25.254.6',MASTER_USER='repl',MASTER_PASSWORD='redhat',MASTER_LOG_FILE="mysql-bin.000001",MASTER_LOG_POS='2086';
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 ''2086'' at line 1
mysql> CHANGE MASTER TO MASTER_HOST='172.25.254.6',MASTER_USER='repl',MASTER_PASSWORD='redhat',MASTER_LOG_FILE="mysql-bin.000001",MASTER_LOG_POS=2086;
Query OK, 0 rows affected, 2 warnings (0.02 sec)

mysql> slave start;
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 'slave start' at line 1
mysql>
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW SLAVE STATUS\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 172.25.254.6
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 2086
Relay_Log_File: mysql3-relay-bin.000002
Relay_Log_Pos: 320
Relay_Master_Log_File: mysql-bin.000001
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: 2086
Relay_Log_Space: 528
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: 1
Master_UUID: 830992bc-605c-11ef-b0f2-000c29c6c80f
Master_Info_File: /data/mysql/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave 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:
1 row in set (0.00 sec)

ERROR:
No query specified

**************/无关/******************

[root@mysql2 ~]# mysql -urpel -predhat
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1045 (28000): Access denied for user 'rpel'@'localhost' (using password: YES)
[root@mysql2 ~]#
[root@mysql2 ~]# mysql -urepl -predhat
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1045 (28000): Access denied for user 'repl'@'localhost' (using password: YES)
[root@mysql2 ~]#
[root@mysql2 ~]# mysql -urepl -predhat -h 172.25.254.6  ##rpel 授权用户
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 23
Server version: 5.7.44-log Source distribution

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@mysql2 ~]# mysql -uhaha -predhat -h 172.25.254.6 ##非授权用户不行。
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1045 (28000): Access denied for user 'haha'@'172.25.254.66' (using password: YES)

##测试slave2是否成功设为了从节点

mater上：

mysql> INSERT INTO folian.userlist values ('user2','123');
Query OK, 1 row affected (0.02 sec)
slave2上：

mysql> SELECT * FROM folian.userlist;
+----------+----------+
| username | password |
+----------+----------+
| folian | 123 | ##原来的也有
| user2 | 123 | ##现在的也有 yes
+----------+----------+
2 rows in set (0.00 sec)

当然slave1上也会有：

mysql> SELECT * FROM folian.userlist;
+----------+----------+
| username | password |
+----------+----------+
| folian | 123 |
| user2 | 123 |
+----------+----------+
2 rows in set (0.02 sec)
这就是一主两从》》》

思考：什么时候主多，什么时候从多？

主多：写入大于读取

从多：读取大于写入

关于SQL_Delay：延迟

延迟复制：作用？

做了一个操作，又返回的机会；

用一个slave作为示例：slave2

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

mysql> CHANGE MASTER TO MASTER_DELAY=60;
Query OK, 0 rows affected (0.00 sec)

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

mysql> SHOW SLAVE STATUS\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 172.25.254.6
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 2354
Relay_Log_File: mysql3-relay-bin.000002
Relay_Log_Pos: 588
Relay_Master_Log_File: mysql-bin.000001
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: 2354
Relay_Log_Space: 796
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: 1
Master_UUID: 830992bc-605c-11ef-b0f2-000c29c6c80f
Master_Info_File: /data/mysql/master.info
SQL_Delay: 60
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave 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:
1 row in set (0.00 sec)

ERROR:
No query specified

延迟已经设置好了为60s；

比如这时，在master上把folian.userlist表中的user2给删了；

mysql> delete from folian.userlist where username='user2';
Query OK, 1 row affected (0.01 sec)
mysql> select * from folian.userlist;
+----------+----------+
| username | password |
+----------+----------+
| folian | 123 |
+----------+----------+
1 row in set (0.00 sec)

然后去slave2上查看：在60秒内发现被删除的数据还在，假如删除是误操作的，可以紧急把库导出来备份恢复。

mysql> select * from folian.userlist;
+----------+----------+
| username | password |
+----------+----------+
| folian | 123 |
+----------+----------+
1 row in set (0.00 sec)

慢查询日志

##查看慢查询日志是否开启

mysql> SHOW variables like "slow%";
+---------------------+-----------------------------+
| Variable_name | Value |
+---------------------+-----------------------------+
| slow_launch_time | 2 |
| slow_query_log | OFF |
| slow_query_log_file | /data/mysql/mysql1-slow.log |
+---------------------+-----------------------------+
3 rows in set (0.04 sec)

#开启或关闭慢查询日志，是在master上

mysql> SET GLOBAL slow_query_log=ON;
#所有的SET都可以写在/etc/my.cnf里

[root@mysql1 ~]# vim /etc/my.cnf
[root@mysql1 ~]# cat /etc/my.cnf
[mysqld]
datadir=/data/mysql
socket=/data/mysql/mysql.sock
symbolic-links=0
log-bin=mysql-bin
server-id=1
slow_query_log=on;

##但是写在my.cnf里要重启后才能生效，还是直接进入mysql里写。

mysql> SET GLOBAL slow_query_log=ON;
Query OK, 0 rows affected (0.02 sec)

mysql> SET GLOBAL slow_query_log=ON;
Query OK, 0 rows affected (0.02 sec)

mysql> SHOW variables like "slow%";
+---------------------+-----------------------------+
| Variable_name | Value |
+---------------------+-----------------------------+
| slow_launch_time | 2 |
| slow_query_log | ON |
| slow_query_log_file | /data/mysql/mysql1-slow.log |
+---------------------+-----------------------------+
3 rows in set (0.01 sec)

#开启了慢查询，还要设置慢查询的时间

mysql> SHOW variables like "long";
Empty set (0.00 sec)

mysql> SHOW variables like "long%";
+-----------------+-----------+
| Variable_name | Value |
+-----------------+-----------+
| long_query_time | 10.000000 | ##这里默认为10s，意思是时间超过10s则视为慢查询
+-----------------+-----------+
1 row in set (0.00 sec)

#测试慢查询

mysql> select sleep (10); #当执行这个操作，要等待十秒才会出现。。。
+------------+
| sleep (10) |
+------------+
| 0 |
+------------+
1 row in set (10.00 sec)
##查看慢查询日志

[root@mysql1 mysql]# cat /data/mysql/mysql1-slow.log
/usr/local/mysql/bin/mysqld, Version: 5.7.44-log (Source distribution). started with:
Tcp port: 3306 Unix socket: /data/mysql/mysql.sock
Time Id Command Argument
# Time: 2024-08-27T01:47:19.777138Z
# User@Host: root[root] @ localhost [] Id: 27
# Query_time: 10.003945 Lock_time: 0.000000 Rows_sent: 1 Rows_examined: 0
SET timestamp=1724723239;
select sleep (10);