Shulou(Shulou.com)05/31 Report--

This article mainly introduces the mysql accidentally deleted table how to do, has a certain reference value, interested friends can refer to, I hope you can learn a lot after reading this article, the following let the editor with you to understand.

Test environment:

5.6.33 and centos6.5

Source code installation library 1:

Yum install "@ development tools" cmake ncurses-devel

Yum install make gcc-c++ cmake bison-devel ncurses-devel

Wget http://101.110.118.23/dev.mysql.com/get/Downloads/MySQL-5.6/mysql-5.6.33.tar.gz

Decompression

Tar xf mysql-5.6.33.tar.gz

Compile

Cmake-DCMAKE_INSTALL_PREFIX=/usr/local/mysql-DMYSQL_DATADIR=/data/mysql/data

Make

Make install

Authorization

Chown mysql:mysql / usr/local/mysql-R

Chown mysql:mysql / data/mysql/data-R

Establish a configuration file

[root@localhost data] # vim / etc/my.cnf

[client]

Port=3306

Socket=/var/lib/mysql/mysql.sock

Default-character-set = utf8

[mysqld]

Port=3306

Basedir=/usr/local/mysql

Datadir=/data/mysql/data

Socket=/var/lib/mysql/mysql.sock

User=mysql

[mysqld_safe]

Log-error=/var/log/mysqld.log

Pid-file=/var/run/mysqld/mysqld.pid

Initialization

/ usr/local/mysql/scripts/mysql_install_db-basedir=-/usr/local/mysql-user=mysql-ldata=/data/mysql/data

It can also be written as

/ usr/local/mysql/scripts/mysql_install_db-- defaults-file=/etc/my.cnf

Start:

/ usr/local/mysql/bin/mysqld_safe-- defaults-file=/etc/my.cnf

Enter the library to establish the test library file

1. Modify / etc/my.cnf to add an open binary log

Log-bin=logbin

2 restart the service

3. Build a database

Create database test default character set utf8

4. Build a table

Create table a (id int (4), name char (10)) engine=innodb

5. Insert data

Insert into a values (1, "t") inserts N.

6. Delete

Delete from emp

7. Start the second library

/ usr/local/mysql/bin/mysqld_safe-- defaults-file=/etc/myserver.cnf

8. Check the binlog to find the recovery point

A. / bin/mysqlbinlog: unknown variable 'default-character-set=utf8' error occurs during the recovery process.

Just comment out the configuration file and there is no need to restart, because at this point mysqlbinlog will call the configuration file twice and restore it after the configuration file is used.

/ usr/local/mysql/bin/mysqlbinlog logbin.000002 > test2.log

Less test2.log

9. Restore to the second library

/ usr/local/mysql3308/bin/mysqlbinlog / data/mysql/data/logbin.000002-- stop-position=735 | / usr/local/mysql3308/bin/mysql-uroot-h227.0.0.1

It is found that one sentence is missing from the recovery. It's okay to use the following.

/ usr/local/mysql3308/bin/mysqlbinlog / data/mysql/data/logbin.000002-- start-position=735-- stop-position=868 | / usr/local/mysql3308/bin/mysql-uroot-h227.0.0.1

Note above: restoring to points 735 and 837 will lose the data of the third item 4jint, so assign to the point above the delete operation 868.

Direct recovery

. / bin/mysqlbinlog / data/mysql/data/logbin.000002-- stop-position=868 | / usr/local/mysql3308/bin/mysql-uroot-h227.0.0.1-P3308

10. Restore to the main library:

1. Direct recovery

/ usr/local/mysql/bin/mysqlbinlog-- stop-position=868 | / usr/local/mysql/bin/mysql-uroot-h227.0.0.1

2. Copy the recovered form to the corresponding library file.

Not done

3. Export the restored library, copy and rename the original library, import the restored library, and then delete the misoperated library before.

Export tabl

/ usr/local/mysql3308/bin/mysqldump-h227.0.0.1-uroot-P3308 test a-- default-character-set=utf8 > a.sql

Original table replication-it may be highly recommended that the original table rename

Create table a_bak as select * from a

Original table rename

Rename table a to b

Import tabl

/ usr/local/mysql/bin/mysql-uroot-h227.0.0.1

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.