2）将 备份时刷新之后的 binary log 利用 mv 命令移动到安全的位置，也就是--master-data=2输出的日志文件，它之前的日志文件都存储到安全的位置：
[root@localhost ~]# head -n 50 gs_ngx_lua_backup.sql -- MySQL dump 10.13 Distrib 5.6.26, for linux-glibc2.5 (i686) -- -- Host: localhost Database: gs ... ...-- -- Position to start replication or point-in-time recovery from -- -- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000027', MASTER_LOG_POS=120;
也就是将 MASTER_LOG_FILE='mysql-bin.000027' 之前的日志都存储到其它位置。
然后执行：purge binary logs to 'mysql-bin.000027' 更新了 mysql-bin.index 中的索引信息，这里并没有删除binary log，因为它们已经被mv走了。
3）下面模拟一个 增量备份：
mysql> delete from user where id=5; Query OK, 1 row affected (0.02 sec) mysql> select * from user; +----+----------+------+-------------+----------+ | id | name | sex | phone | addr | +----+----------+------+-------------+----------+ | 1 | yuanfang | 1 | 14465463786 | hangzhou | | 2 | Andy | 0 | 14465463786 | beijing | | 3 | peter | 0 | 14465463786 | Hongkong | | 4 | lexin | 1 | 36565634 | shenzhen | +----+----------+------+-------------+----------+ 4 rows in set (0.00 sec)
mysql> flush logs; Query OK, 0 rows affected (0.11 sec) mysql> show binary logs; +------------------+-----------+ | Log_name | File_size | +------------------+-----------+ | mysql-bin.000027 | 370 | | mysql-bin.000028 | 120 | +------------------+-----------+ 2 rows in set (0.00 sec)
这里 flush logs 进行增量备份，然后将增量备份的 bianry log 文件 mysql-bin.000027 也存储起来。
然后在进行一条 delete 语句：
mysql> select * from user; +----+----------+------+-------------+----------+ | id | name | sex | phone | addr | +----+----------+------+-------------+----------+ | 1 | yuanfang | 1 | 14465463786 | hangzhou | | 2 | Andy | 0 | 14465463786 | beijing | | 3 | peter | 0 | 14465463786 | Hongkong | +----+----------+------+-------------+----------+ 3 rows in set (0.00 sec)
到这里数据库的最新状态是：user 表只有3条记录。
然后我们同样将 mysql-bin.000028 也存储起来。
4）然后我们删除 data-dir 目录中的所有文件，然后开始还原：
[root@localhost mysql]# pwd /var/lib/mysql [root@localhost mysql]# mv ./* /backup/before_delete/
此时 data-dir 目录是空的。然后我们试图使用 mysqladmin -uroot -p shutdown 来关闭mysqld，发现早不到 mysql.sock 无法关闭，我们只好使用
killall mysqld
来关闭。
5）然后进行数据库的初始化，然后开始还原：
进入到 /usr/local/mysql/script 目录，然后执行初始化：
./mysql_install_db --user=mysql --datadir=/var/lib/mysql --basedir=/usr/local/mysql
[root@localhost scripts]# ./mysql_install_db --user=mysql --datadir=/var/lib/mysql --basedir=/usr/local/mysql Installing MySQL system tables...2015-10-24 16:45:19 0 [Note] /usr/local/mysql/bin/mysqld (mysqld 5.6.26-log) starting as process 5870 .. . 2015-10-24 16:45:19 5870 [Note] InnoDB: Using mutexes to ref count buffer pool pages 2015-10-24 16:45:19 5870 [Note] InnoDB: The InnoDB memory heap is disabled 2015-10-24 16:45:19 5870 [Note] InnoDB: Mutexes and rw_locks use InnoDB's own implementation 2015-10-24 16:45:19 5870 [Note] InnoDB: Memory barrier is not used 2015-10-24 16:45:19 5870 [Note] InnoDB: Compressed tables use zlib 1.2.3 2015-10-24 16:45:19 5870 [Note] InnoDB: Using Linux native AIO 2015-10-24 16:45:19 5870 [Note] InnoDB: Not using CPU crc32 instructions 2015-10-24 16:45:19 5870 [Note] InnoDB: Initializing buffer pool, size = 6.0M 2015-10-24 16:45:19 5870 [Note] InnoDB: Completed initialization of buffer pool 2015-10-24 16:45:20 5870 [Note] InnoDB: The first specified data file ./ibdata1 did not exist: a new database to be created! 2015-10-24 16:45:20 5870 [Note] InnoDB: Setting file ./ibdata1 size to 12 MB 2015-10-24 16:45:20 5870 [Note] InnoDB: Database physically writes the file full: wait... 2015-10-24 16:45:20 5870 [Note] InnoDB: Setting log file ./ib_logfile101 size to 48 MB 2015-10-24 16:45:21 5870 [Note] InnoDB: Setting log file ./ib_logfile1 size to 48 MB 2015-10-24 16:45:23 5870 [Note] InnoDB: Renaming log file ./ib_logfile101 to ./ib_logfile0 2015-10-24 16:45:23 5870 [Warning] InnoDB: New log files created, LSN=45781 2015-10-24 16:45:23 5870 [Note] InnoDB: Doublewrite buffer not found: creating new 2015-10-24 16:45:23 5870 [Note] InnoDB: Doublewrite buffer created 2015-10-24 16:45:23 5870 [Note] InnoDB: 128 rollback segment(s) are active. 2015-10-24 16:45:23 5870 [Warning] InnoDB: Creating foreign key constraint system tables. 2015-10-24 16:45:23 5870 [Note] InnoDB: Foreign key constraint system tables created 2015-10-24 16:45:23 5870 [Note] InnoDB: Creating tablespace and datafile system tables. 2015-10-24 16:45:23 5870 [Note] InnoDB: Tablespace and datafile system tables created. 2015-10-24 16:45:23 5870 [Note] InnoDB: Waiting for purge to start 2015-10-24 16:45:23 5870 [Note] InnoDB: 5.6.26 started; log sequence number 0 2015-10-24 16:45:28 5870 [Note] Binlog end 2015-10-24 16:45:28 5870 [Note] InnoDB: FTS optimize thread exiting. 2015-10-24 16:45:28 5870 [Note] InnoDB: Starting shutdown... 2015-10-24 16:45:30 5870 [Note] InnoDB: Shutdown completed; log sequence number 1625977 OK Filling help tables...2015-10-24 16:45:31 0 [Note] /usr/local/mysql/bin/mysqld (mysqld 5.6.26-log) starting as process 5893 ... 2015-10-24 16:45:31 5893 [Note] InnoDB: Using mutexes to ref count buffer pool pages 2015-10-24 16:45:31 5893 [Note] InnoDB: The InnoDB memory heap is disabled 2015-10-24 16:45:31 5893 [Note] InnoDB: Mutexes and rw_locks use InnoDB's own implementation 2015-10-24 16:45:31 5893 [Note] InnoDB: Memory barrier is not used 2015-10-24 16:45:31 5893 [Note] InnoDB: Compressed tables use zlib 1.2.3 2015-10-24 16:45:31 5893 [Note] InnoDB: Using Linux native AIO 2015-10-24 16:45:31 5893 [Note] InnoDB: Not using CPU crc32 instructions 2015-10-24 16:45:31 5893 [Note] InnoDB: Initializing buffer pool, size = 6.0M 2015-10-24 16:45:31 5893 [Note] InnoDB: Completed initialization of buffer pool 2015-10-24 16:45:31 5893 [Note] InnoDB: Highest supported file format is Barracuda. 2015-10-24 16:45:32 5893 [Note] InnoDB: 128 rollback segment(s) are active. 2015-10-24 16:45:32 5893 [Note] InnoDB: Waiting for purge to start 2015-10-24 16:45:32 5893 [Note] InnoDB: 5.6.26 started; log sequence number 1625977 2015-10-24 16:45:34 5893 [Note] Binlog end 2015-10-24 16:45:34 5893 [Note] InnoDB: FTS optimize thread exiting. 2015-10-24 16:45:34 5893 [Note] InnoDB: Starting shutdown... 2015-10-24 16:45:35 5893 [Note] InnoDB: Shutdown completed; log sequence number 1625987 OK To start mysqld at boot time you have to copy support-files/mysql.server to the right place for your system PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER ! To do so, start the server, then issue the following commands: /usr/local/mysql/bin/mysqladmin -u root password 'new-password' /usr/local/mysql/bin/mysqladmin -u root -h localhost.localdomain password 'new-password' Alternatively you can run: /usr/local/mysql/bin/mysql_secure_installation which will also give you the option of removing the test databases and anonymous user created by default. This is strongly recommended for production servers. See the manual for more instructions. You can start the MySQL daemon with: cd . ; /usr/local/mysql/bin/mysqld_safe & You can test the MySQL daemon with mysql-test-run.pl cd mysql-test ; perl mysql-test-run.pl Please report any problems at http://bugs.mysql.com/ The latest information about MySQL is available on the web at http://www.mysql.com Support MySQL by buying support/licenses at com WARNING: Found existing config file /usr/local/mysql/my.cnf on the system. Because this file might be in use, it was not replaced, but was used in bootstrap (unless you used --defaults-file) and when you later start the server. The new default config file was created as /usr/local/mysql/my-new.cnf, please compare it with your file and take the changes you need. WARNING: Default config file /etc/my.cnf exists on the system This file will be read by default by the MySQL server If you do not want to use this, either remove it, or use the --defaults-file argument to mysqld_safe when starting the server [root@localhost scripts]#
./mysql_install_db初始化成功之后，执行：
/usr/local/mysql/bin/mysql_secure_installation
来进行密码设置。这一步可能会报错：找不到 /tmp/mysql.sock 文，解决方法，在/etc/my.cnf 中的[client], [mysql], [mysqld] 都进行下面的设置：
socket=/tmp/mysql.sock
然后重新执行：/usr/local/mysql/bin/mysql_secure_installation 就行了。
初始化完成之后，我们使用备份的sql脚本来进行恢复：
[root@localhost ~]# mysql -uroot -p < gs_ngx_lua_backup.sql
执行完成之后，发现 gs 和 ngx_lua 数据库都还原出来了，但是他们的数据不是最新的数据：