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

This article mainly introduces "operating system level recovery mysql database method is what", in daily operation, I believe many people in the operating system level recovery mysql database method is what problem there is doubt, Xiaobian consulted all kinds of information, sorted out simple and easy to use operation method, hope to answer everyone "operating system level recovery mysql database method is what" doubt helpful! Next, please follow the small series to learn together!

Restore the mysql database by copying a directory of libraries in mysql.

Problem Description:

At 10:00 a.m. on January 10, 2018, the server of our oa system suddenly went down. We tried to start the server, but there was a problem with the disk. Finally, we could only use the monitor to connect directly to the server. Crt could not connect remotely. It could not ssh to other servers or scp files to other servers. We could only copy the backups of programs and databases by moving the hard disk. Every night, mysqldump sql files. If you use it to recover data, This morning's punch card was gone, so I tried to recover the data using the operating system directory.

We know that each database in mysql corresponds to a directory, and then all the tables under this database are under this directory. If this mysql opens an independent table space, theoretically, we can use this directory to recover data.

The following is the specific process: The following process can be successful if the source mysql opens an independent table space, that is, the parameter innodb_file_per_table =on

First, create the library that needs to be restored at the destination.

mysql> create database oa;

Query OK, 1 row affected (0.03 sec)

Second: according to yesterday's backup sql file, restore the table structure under the oa library, you must do this step, otherwise there is no information about these tables in the mysql data dictionary, then these tables cannot be used, that is, you cannot perform dml operations;

mysql> use oa;

mysql> source oa-20180109.sql;

mysql> show tables; #To see if we do have a table structure, we just need to create it.

+------------------------------------------+

| Tables_in_oa |

+------------------------------------------+

| 20161213_2_sch_assess_mark_opponent_copy |

| 20161213sch_assess_copy |

| 20161213sch_assess_mark_column_copy |

| 20161213sch_assess_mark_copy |

| 20161213sch_assess_mark_opponent_copy |

| 20161213sch_assess_order_copy |

| 20161213sch_assess_player_copy |

| #Tableau_sid_000011DF_2_Group |

| #Tableau_sid_000011E1_4_Group |

| #Tableau_sid_000011EE_4_Group |

| #Tableau_sid_000011EE_5_Group |

| BDF2_BLOB_STORE |

| BDF2_CLOB_STORE |

| BDF2_COMPANY |

| BDF2_COMPONENT |

3: copy the oa directory at the operating system level to overwrite the current oa directory;

bogon:root@/>ps -ef | grep mysql

root 20076 7826 0 21:44 pts/2 00:00:00 grep --color=auto mysql

root 25748 1 0 2017 ? 00:00:00 /bin/sh /usr/local/mysql/bin/mysqld_safe --datadir=/mysql/data/data--pid-file=/var/run/mysql/mysql.pid

bogon:root@/> cd/mysql/data/data

bogon:root@/mysql/data/data>ll

total 1126436

-rw-rw----. 1 mysql mysql 56 Dec 15 11:46 auto.cnf

-rw-rw----. 1 mysql mysql 79691776 Jan 10 21:32 ibdata1

-rw-rw----. 1 mysql mysql 536870912 Jan 10 21:32 ib_logfile0

-rw-rw----. 1 mysql mysql 536870912 Jan 10 21:32 ib_logfile1

drwx------. 2 mysql mysql 4096 Jan 4 16:38 liuwenhe

drwx------. 2 mysql mysql 4096 Dec 20 22:10 mysql

drwx------. 2 mysql mysql 12288 Jan 10 21:31 oa

drwx------. 2 mysql mysql 4096 Dec 20 22:10 performance_schema

drwx------. 2 mysql mysql 6 Dec 20 22:10 test

bogon:root@/>cp -r oa_bak/ /mysql/data/data

bogon:root@/mysql/data/data>ll

total 1126452

-rw-rw----. 1 mysql mysql 56 Dec 15 11:46 auto.cnf

-rw-rw----. 1 mysql mysql 79691776 Jan 10 21:32 ibdata1

-rw-rw----. 1 mysql mysql 536870912 Jan 10 21:32 ib_logfile0

-rw-rw----. 1 mysql mysql 536870912 Jan 10 21:32 ib_logfile1

drwx------. 2 mysql mysql 4096 Jan 4 16:38 liuwenhe

drwx------. 2 mysql mysql 4096 Dec 20 22:10 mysql

drwx------. 2 mysql mysql 12288 Jan 10 21:31 oa

drwx------. 2 root root 12288 Jan 10 21:46 oa_bak

drwx------. 2 mysql mysql 4096 Dec 20 22:10 performance_schema

drwx------. 2 mysql mysql 6 Dec 20 22:10 test

bogon:root@/mysql/data/data>rm -rf oa

bogon:root@/mysql/data/data>mv oa_bak oa

IV: Be sure to modify the permissions of the OA directory,

bogon:root@/mysql/data/data>chown -R mysql:mysql oa

5: Check whether the database is normal

Check that today's data is already available

mysql> use oa;

mysql> select * from LOG_ATTENDANCE where User_Uid='liuwenhe' order by 4 desc limit 2;

+---------+----------+---------------+---------------------+

| Sort_ID | User_Uid | IP_Address | Create_Date |

+---------+----------+---------------+---------------------+

| 213614 | liuwenhe | 192.168.8.172 | 2018-01-10 08:12:50 |

| 213501 | liuwenhe | 192.168.8.172 | 2018-01-09 17:47:59 |

+---------+----------+---------------+---------------------+

Try to do add, delete and change the operation, it is possible;

mysql> create table test (id int);

Query OK, 0 rows affected (0.24 sec)

mysql> insert into test values (1);

Query OK, 1 row affected (0.04 sec)

Six: the last step is crucial,

Because I am in a master copy of mysql recovery, this operation I only operated on an instance, so I intend to first mysqldump out of this library, and then delete this library, and then restore it again, to ensure that the two sets of database data consistency;

bogon:root@/mysql/data/data>mysqldump -uroot -pliuwenhe oa >oa.sql

However, there is a problem deleting this library.(Later, when I simulated this process, there was no error, very strange!)

mysql> drop database oa;

ERROR 1010 (HY000): Error dropping database (can't rmdir './ oa/', errno: 17)

So delete the directory directly from the operating system level and delete both instances

bogon:root@/mysql/data/data>rm -rf oa

and then create a new upper library oa and restore the most recent data;

mysql> create database oa;

mysql> source oa.sql;

So far, the operation of recovering the database by using the files of the operating system is completed

At this point, the study of "what is the method to recover mysql database at the operating system level" is over, hoping to solve everyone's doubts. Theory and practice can better match to help you learn, go and try it! If you want to continue learning more relevant knowledge, please continue to pay attention to the website, Xiaobian will continue to strive to bring more practical articles for everyone!

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.