源库这边跑一个sysbench,为了测试之后kill -HUP是不是会有影响1
2
3
4
5
6
7
8
9
10
11sysbench /usr/share/sysbench/oltp_read_write.lua --mysql-user=root --mysql-password=mysql --mysql-port=3306 \
--mysql-socket=/data/mysqldata/3306/mysql.sock --mysql-host=localhost \
--mysql-db=sysbenchtest --tables=10 --table-size=5000000 --threads=30 \
--events=5000000 --report-interval=5 --db-driver=mysql prepare
sysbench \
/usr/share/sysbench/oltp_read_write.lua \
--mysql-user=root --mysql-password=mysql --mysql-port=3306 \
--mysql-socket=/data/mysqldata/3306/mysql.sock --mysql-host=localhost \
--mysql-db=sysbenchtest --tables=10 --table-size=5000000 \
--threads=30 --report-interval=5 --time=7000 --db-driver=mysql run > binlog_off.txt
把忘记密码的库的mysql.user表传到一个知道密码的测试库1
2
3
4
5[root@uz22199 mysql]# scp -p user.* 10.4.3.100:/data/mysqldata/3306/data/fandb/
root@10.4.3.100's password:
user.frm 100% 11KB 10.6KB/s 00:00
user.MYD 100% 736 0.7KB/s 00:00
user.MYI 100% 4096 4.0KB/s 00:00
生成一下insert语句,后面用1
2
3
4[root@test43100 ~]# mysqldump --user=root --password='mysql' fandb user --where="host='localhost' and user='root'" |grep INSERT
mysqldump: [Warning] Using a password on the command line interface can be insecure.
Warning: A partial dump from a server that has GTIDs will by default include the GTIDs of all transactions, even those that changed suppressed parts of the database. If you don't want to restore GTIDs, pass --set-gtid-purged=OFF. To make a complete dump, pass --all-databases --triggers --routines --events.
INSERT INTO `user` VALUES ('localhost','root','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','','','','',0,0,0,0,'mysql_native_password','*81F5E21E35407D884A6CD4A731AEBFB6AF209E1B','N','2017-08-04 08:12:53',NULL,'N');
新用户密码mysql1
2
3
4
5
6root@mysqldb 15:04: [(none)]> select password('mysql');
+-------------------------------------------+
| password('mysql') |
+-------------------------------------------+
| *E74858DB86EBA20BC33D0AECAE8A8108C56B17FA |
+-------------------------------------------+
新插入一个用户,把上面的insert改一下1
INSERT INTO `user` VALUES ('localhost','fanboshi','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','','','','',0,0,0,0,'mysql_native_password','*E74858DB86EBA20BC33D0AECAE8A8108C56B17FA','N','2017-08-04 08:12:53',NULL,'N');
关闭表1
root@mysqldb 15:05: [(none)]> flush tables;
传回源库1
2
3
4
5[root@test43100 fandb]# scp -p user.* 10.4.1.45:/data/mysqldata/3306/data/mysql/
root@10.4.1.45's password:
user.frm 100% 11KB 10.6KB/s 00:00
user.MYD 100% 864 0.8KB/s 00:00
user.MYI
无法登陆1
2
3[root@uz22199 ~]# mysql -ufanboshi -pmysql
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1045 (28000): Access denied for user 'fanboshi'@'localhost' (using password: YES)
flush privileges1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18root@mysqldb 14:53: [mysql]> flush privileges;
Query OK, 0 rows affected (0.82 sec)
[root@uz22199 ~]# mysql -ufanboshi -pmysql
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 44
Server version: 5.7.18-log MySQL Community Server (GPL)
Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.
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.
fanboshi@mysqldb 14:54: [(none)]> quit
flush privileges完了就可以登陆了,不知道只这样做可以不可以,还是按吴总来吧
看一下现在mysqld进程号1
2
3
4
5[root@uz22199 ~]# ps -ef| grep mysqld
root 14676 1 0 Sep11 ? 00:00:00 /bin/sh /usr/local/mysql/bin/mysqld_safe --relay-log-recovery=0
mysql 16188 14676 2 Sep11 ? 00:33:24 /usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql --datadir=/data/mysqldata/3306/data --plugin-dir=/usr/local/mysql/lib/plugin --user=mysql --relay-log-recovery=0 --log-error=/data/mysqldata/3306/data/../error.log --open-files-limit=65535 --pid-file=/data/mysqldata/3306/mysql.pid --socket=/data/mysqldata/3306/mysql.sock --port=3306
root 23497 21466 1 13:49 pts/4 00:00:55 sysbench /usr/share/sysbench/oltp_read_write.lua --mysql-user=root --mysql-password=mysql --mysql-port=3306 --mysql-socket=/data/mysqldata/3306/mysql.sock --mysql-host=localhost --mysql-db=sysbenchtest --tables=10 --table-size=5000000 --threads=2 --report-interval=5 --time=7000 --db-driver=mysql run
root 23775 21421 0 14:54 pts/0 00:00:00 grep mysqld
kill -HUP 重新加载配置文件1
[root@uz22199 ~]
看一下mysqld pid没变1
2
3
4
5[root@uz22199 ~]# ps -ef| grep mysqld
root 14676 1 0 Sep11 ? 00:00:00 /bin/sh /usr/local/mysql/bin/mysqld_safe --relay-log-recovery=0
mysql 16188 14676 2 Sep11 ? 00:33:26 /usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql --datadir=/data/mysqldata/3306/data --plugin-dir=/usr/local/mysql/lib/plugin --user=mysql --relay-log-recovery=0 --log-error=/data/mysqldata/3306/data/../error.log --open-files-limit=65535 --pid-file=/data/mysqldata/3306/mysql.pid --socket=/data/mysqldata/3306/mysql.sock --port=3306
root 23497 21466 1 13:49 pts/4 00:00:55 sysbench /usr/share/sysbench/oltp_read_write.lua --mysql-user=root --mysql-password=mysql --mysql-port=3306 --mysql-socket=/data/mysqldata/3306/mysql.sock --mysql-host=localhost --mysql-db=sysbenchtest --tables=10 --table-size=5000000 --threads=2 --report-interval=5 --time=7000 --db-driver=mysql run
root 23782 21421 0 14:55 pts/0 00:00:00 grep mysqld
sysbench没有报错