MySQL忘记密码处理方法,无需重启

源库这边跑一个sysbench,为了测试之后kill -HUP是不是会有影响

1
2
3
4
5
6
7
8
9
10
11
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 \
--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');

阅读全文

MySQL不完全恢复

不完全恢复

通过做Slave恢复

找到问题语句的位置,这里是1315

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
#170911 14:42:19 server id 330641 end_log_pos 2508 CRC32 0xf697b7bb Xid = 215
COMMIT/*!*/;
# at 2508
#170911 14:50:56 server id 330641 end_log_pos 2573 CRC32 0x8309dea0 GTID last_committed=9 sequence_number=10
SET @@SESSION.GTID_NEXT= '5c351518-78ec-11e7-8e7a-005056a610c3:1315'/*!*/;
# at 2573
#170911 14:50:56 server id 330641 end_log_pos 2693 CRC32 0xb06b9074 Query thread_id=25 exec_time=1 error_code=0
use `fandb`/*!*/;
SET TIMESTAMP=1505112656/*!*/;
DROP TABLE `users` /* generated by server */
/*!*/;
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
# End of log file
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

那一份备份,恢复出来,启动,reset slave all;然后change master

1
2
3
4
5
6
7
8
9
10
change master to
master_host='10.4.3.100',
master_user='repl',
master_password='repl',
master_port=3306,
master_auto_position=1;
重点
start slave sql_thread until SQL_BEFORE_GTIDS='5c351518-78ec-11e7-8e7a-005056a610c3:1315';
start slave io_thread;

阅读全文

通过mysqlbinlog --skip-gtids恢复后再备份可能造成的坑

通过mysqlbinlog –skip-gtids恢复后再备份可能造成的坑

版本

1
2
3
4
5
6
7
8
[root@uz22199 backup]# innobackupex --version
innobackupex version 2.4.8 Linux (x86_64) (revision id: 97330f7)
[root@uz22199 backup]# mysql -e"select @@version"
+------------+
| @@version |
+------------+
| 5.7.18-log |
+------------+

源库

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
表结构与数据
root@mysqldb 21:51: [fandb]> show create table users\G
*************************** 1. row ***************************
Table: users
Create Table: CREATE TABLE `users` (
`email` varchar(10) DEFAULT NULL,
UNIQUE KEY `email` (`email`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
1 row in set (0.00 sec)
root@mysqldb 18:43: [fandb]> select* from users;
+-------+
| email |
+-------+
| 1 |
| 10 |
| 20 |
| 30 |
| 5 |
+-------+
插入一条数据
insert into users values(50); --GTID=1297
再删掉
delete from users where email=50; ----GTID=1298
当前Executed_Gtid_Set
root@mysqldb 18:35: [fandb]> show master status;
+------------------+----------+--------------+------------------+---------------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+---------------------------------------------+
| mysql-bin.000005 | 495 | | | 5c351518-78ec-11e7-8e7a-005056a610c3:1-1298 |
+------------------+----------+--------------+------------------+---------------------------------------------+
1 row in set (0.00 sec)

源库再次应用一下已经执行过得binlog, 再次应用insert into users values(50); 这一条

这里先不考虑有没有可能这样子去恢复数据,只做实验

阅读全文

在datadir外创建InnoDB表

在datadir外创建表

要在MySQL datadir外的特定位置创建新的InnoDB file-per-table tablespace,请在create table时指定DATA DIRECTORY = absolute_path_to_directory子句

提前规划好位置,因为无法使用alter语句修改一个表的DATA DIRECTORY属性.

MySQL会在目标目录中创建一个对应于数据库名称的子目录,并在改目录中创建表的.ibd文件用于存储表数据.在MySQL datadir目录下的数据库目录中,MySQL创建一个包含表的路径名称的table_name.isl文件. .isl文件由MySQL处理,像一个符号链接(不过InnoDB表不支持实际的符号链接)

示例:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
版本
[root@test43100 ~]# cat /etc/redhat-release
CentOS release 6.4 (Final)
[root@test43100 ~]# mysql -e "\s"
--------------
mysql Ver 14.14 Distrib 5.7.18, for linux-glibc2.5 (x86_64) using EditLine wrapper
root@mysqldb 11:00: [(none)]> use fandb
Database changed
root@mysqldb 11:00: [fandb]> SHOW VARIABLES LIKE 'innodb_file_per_table';
+-----------------------+-------+
| Variable_name | Value |
+-----------------------+-------+
| innodb_file_per_table | ON |
+-----------------------+-------+
1 row in set (0.00 sec)
root@mysqldb 11:00: [fandb]> create table t_out(id int auto_increment primary key) data directory='/data/outdir';
Query OK, 0 rows affected (0.37 sec)
查看目标目录
[mysql@test43100 data]$ tree outdir/
outdir/
└── fandb
└── t_out.ibd
查看datadir
[root@test43100 fandb]# ls -lt
total 412
-rw-r----- 1 mysql mysql 28 Sep 6 11:03 t_out.isl
-rw-r----- 1 mysql mysql 8556 Sep 6 11:03 t_out.frm
包含.isl和.frm文件

您还可以将CREATE TABLE … TABLESPACE与DATA DIRECTORY子句结合使用,以便在MySQL数据目录之外创建一个file-per-table tablespace。 为此,您必须指定innodb_file_per_table作为表空间名称。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
root@mysqldb 11:03: [fandb]> create table t_out2(id int auto_increment primary key) TABLESPACE = innodb_file_per_table data directory='/data/outdir';
Query OK, 0 rows affected (0.42 sec)
查看目标目录
[mysql@test43100 data]$ tree outdir/
outdir/
└── fandb
├── t_out2.ibd
└── t_out.ibd
查看datadir
[root@test43100 fandb]# ls -lt
total 428
-rw-r----- 1 mysql mysql 29 Sep 6 11:08 t_out2.isl
-rw-r----- 1 mysql mysql 8556 Sep 6 11:08 t_out2.frm

使用第二章方法无需启用innodb_file_per_table

使用说明

  • MySQL最初保持.ibd文件打开,阻止您卸载设备,但如果服务器正忙,最终可能会关闭该表。 当MySQL运行时,请注意不要意外卸载外部设备,或者在设备断开连接时启动MySQL。 当相关的.ibd文件丢失时尝试访问表会导致严重的错误,需要重新启动服务器。
    如果.ibd文件仍然不在预期路径,服务器重新启动可能会失败。 在这种情况下,请手动删除数据库目录中的table_name.isl文件,并在重新启动后执行DROP TABLE以删除.frm文件,并从数据字典中删除有关该表的信息。

  • Before tables on an NFS-mounted volume, review potential issues outlined in Using NFS with MySQL.

  • If you use an LVM snapshot, file copy, or other file-based mechanism to back up the .ibd file, always use the FLUSH TABLES … FOR EXPORT statement first to make sure all changes that were buffered in memory are flushed to disk before the backup occurs.

  • DATA DIRECTORY子句是使用符号链接的一个支持的替代方法.InnoDB直接使用符号链接是不支持的

innobackupex遇到的坑

stream选择使用tar方式压缩到时slave_info信息不完整

版本

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
[root@test1 backup]# innobackupex --version
innobackupex version 2.4.8 Linux (x86_64) (revision id: 97330f7)
[root@test1 backup]# cat /etc/redhat-release
CentOS release 6.4 (Final)
[root@test1 backup]# gzip --version
gzip 1.3.12
Copyright (C) 2007 Free Software Foundation, Inc.
Copyright (C) 1993 Jean-loup Gailly.
This is free software. You may redistribute copies of it under the terms of
the GNU General Public License <http://www.gnu.org/licenses/gpl.html>.
There is NO WARRANTY, to the extent permitted by law.
Written by Jean-loup Gailly.
[root@test1 backup]# mysql -e"\s"
--------------
mysql Ver 14.14 Distrib 5.7.18, for linux-glibc2.5 (x86_64) using EditLine wrapper
Connection id: 11
Current database:
Current user: root@localhost
SSL: Not in use
Current pager: stdout
Using outfile: ''
Using delimiter: ;
Server version: 5.7.18-log MySQL Community Server (GPL)
Protocol version: 10
Connection: Localhost via UNIX socket
Server characterset: utf8mb4
Db characterset: utf8mb4
Client characterset: utf8mb4
Conn. characterset: utf8mb4
UNIX socket: /data/mysqldata/3306/mysql.sock
Uptime: 15 min 52 sec
Threads: 3 Questions: 61 Slow queries: 0 Opens: 116 Flush tables: 5 Open tables: 0 Queries per second avg: 0.064
--------------

备份命令

1
innobackupex --user=backup --password='backup' --slave-info --stream=tar /tmp | gzip -> /data/mysqldata/backup/xtra_full.tar.gz

期望得到的完整信息

1
2
3
[root@test1 slave_info]# more xtrabackup_slave_info
SET GLOBAL gtid_purged='5c351518-78ec-11e7-8e7a-005056a610c3:1-1164';
CHANGE MASTER TO MASTER_AUTO_POSITION=1;

阅读全文

主从传输表空间的坑

主库import tablespace只会在binlog中记录alter table xxx import tablespace语句,而不会记录表中的数据的插入语句

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
[mysql@master2 ~]$ mysqlbinlog -vv --base64-output=decode-rows /data/mysqldata/3306/binlog/mysql-bin.000013 --start-position=694
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 694
#170713 7:46:11 server id 23306 end_log_pos 759 CRC32 0xfa604449 GTID last_committed=2 sequence_number=3
SET @@SESSION.GTID_NEXT= '5691c701-382a-11e5-bbc4-000c293d13e1:19'/*!*/;
# at 759
#170713 7:46:11 server id 23306 end_log_pos 869 CRC32 0x35860c26 Query thread_id=8 exec_time=0 error_code=0
use `fandb`/*!*/;
SET TIMESTAMP=1499903171/*!*/;
SET @@session.pseudo_thread_id=8/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=1075838976/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!\C utf8mb4 *//*!*/;
SET @@session.character_set_client=45,@@session.collation_connection=45,@@session.collation_server=45/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
alter table dept import tablespace
/*!*/;
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
# End of log file
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

假如在源端flush table xxx for export后,只将xxx.{ibd,cfg}拷贝到主库,那么当主库alter table xxx discard tablespace时,从库也会执行discard tablespace
而当主库执行alter table xxx import tablespace时,由于主库有拷贝过来的xxx.{ibd,cfg},所以可以执行成功,而从库没有,会失败

1
2
3
2017-07-10T21:48:13.649264Z 25 [Warning] Slave: InnoDB: ALTER TABLE `fandb`.`dept4` IMPORT TABLESPACE failed with error 44 : 'Tablespace not found' Error_code: 1816
2017-07-10T21:48:13.649293Z 25 [Warning] Slave: Tablespace is missing for table `fandb`.`dept4`. Error_code: 1812
2017-07-10T21:48:13.649321Z 25 [ERROR] Error running query, slave SQL thread aborted. Fix the problem, and restart the slave SQL thread with "SLAVE START". We stopped at log 'mysql-bin.000001' position 1015

slave会停止

阅读全文


Powered by Hexo and Hexo-theme-hiker

Copyright © 2013 - 2018 Fan() All Rights Reserved.

访客数 : | 访问量 :

#