This is the first time I have published it. What are the shortcomings and what opinions you can exchange with each other.
This operation is for CentOS 7 The database mariadb installation and user password settings will not be described in detail above.
1. Suppose a company backs up files every night in the early morning. So we need to create a backup in advance
(1). Create a library named kk first, and then view the Library:
MariaDB [(none)]> create database kk; Query OK, 1 row affected (0.00 sec) MariaDB [(none)]> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | kk | | mysql | | performance_schema | | test | +--------------------+ 5 rows in set (0.00 sec)
(2). Create a table named "student" in the kk library and set it to use Chinese;
MariaDB [(none)]> use kk;
Database changed
MariaDB [kk]> CREATE TABLE `student` (
-> `id` int(10) NOT NULL,
-> `name` char(20) NOT NULL,
-> `age` tinyint(2) NOT NULL DEFAULT '0',
-> `dept` varchar(16) DEFAULT NULL
-> ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
Query OK, 0 rows affected (0.01 sec)
(3). Add data to the table;
MariaDB [kk]> insert into student values(6,'Old boy',32,'python linux net sec'); Query OK, 1 row affected, 1 warning (0.01 sec) MariaDB [kk]> insert into student values(1,'Old boy',32,'python linux net sec'); Query OK, 1 row affected, 1 warning (0.00 sec) MariaDB [kk]> select * from student; +----+-----------+-----+------------------+ | id | name | age | dept | +----+-----------+-----+------------------+ | 6 | Old boy | 32 | python linux net | | 1 | Old boy | 32 | python linux net | +----+-----------+-----+------------------+ 2 rows in set (0.00 sec)
(4) At this point, create a backup, use mysqldump -B, and point the created backup to the opt file
[root@oldboy ~]# mysqldump -uroot -poldboy123 kk -B|gzip >/opt/bak_kk.sql.gz
(5). At work during the day, someone must have written data into the database, so he wrote another one;
MariaDB [kk]> insert into student values(5,'Qiao Feng',32,'python linux net sec'); ERROR 2006 (HY000): MySQL server has gone away No connection. Trying to reconnect... Connection id: 2 Current database: kk MariaDB [(none)]> select * from kk.student; +----+-----------+-----+------------------+ | id | name | age | dept | +----+-----------+-----+------------------+ | 6 | Old boy | 32 | python linux net | | 1 | Old boy | 32 | python linux net | | 5 | Qiao Feng | 32 | python linux net | +----+-----------+-----+------------------+
The boss thought the library was useless and deleted it That is, delete the kk library.
Five minutes later, the website of the operation Department couldn't be opened Technology development can't connect to the database. The database is gone O & M DBA, take a look show databases; No kk
(7). At this time, open the binlog log and add the following to mysql:
[mysqld]
log-bin = /var/log/mariadb/oldboy-bin
This is the file to open the binlog log
[root@oldboy ~]# vim /etc/my.cnf.d/server.cnf # # These groups are read by MariaDB server. # Use it for options that only the server (but not clients) should see # # See the examples of server my.cnf files in /usr/share/mysql/ # # this is read by the standalone daemon and embedded servers [server] # this is only for the mysqld standalone daemon [mysqld] log-bin = /var/log/mariadb/oldboy-bin # this is only for embedded server [embedded] # This group is only read by MariaDB-5.5 servers. # If you use the same .cnf file for MariaDB of different versions, # use this group for options that older servers don't understand [mysqld-5.5] # These two groups are only read by MariaDB servers, not by MySQL. # If you use the same .cnf file for MySQL and MariaDB, # you can put MariaDB-only options here [mariadb] [mariadb-5.5] ~ ~
(8). After that, restart mariadb, and then go to cd /var/log/mariadb to view the created log file. 00000 1 is the first created log file.
[root@oldboy ~]# systemctl restart mariadb [root@oldboy ~]# cd /var/log/mariadb/ [root@oldboy mariadb]# ll Total consumption 16 -rw-r-----. 1 mysql mysql 4925 8 August 11:10 mariadb.log -rw-rw----. 1 mysql mysql 463 8 August 11:11 oldboy-bin.000001 -rw-rw----. 1 mysql mysql 35 8 August 11:10 oldboy-bin.index
(9). cp the var/log/mariadb file to / opt / to prevent the recovered data from being written to binlog again, otherwise a lot of useless data will be added
root@oldboy ~]# cd /var/log/mariadb/ [root@oldboy mariadb]# ll Total consumption 16 -rw-r-----. 1 mysql mysql 4925 8 August 11:10 mariadb.log -rw-rw----. 1 mysql mysql 463 8 August 11:11 oldboy-bin.000001 -rw-rw----. 1 mysql mysql 35 8 August 11:10 oldboy-bin.index [root@oldboy mariadb]# cp * /opt [root@oldboy mariadb]# cd /opt/ [root@oldboy opt]# ll Total consumption 20 -rw-r--r--. 1 root root 829 8 August 11:06 bak_kk.sql.gz -rw-r-----. 1 root root 4925 8 August 11:14 mariadb.log -rw-r-----. 1 root root 540 8 August 11:14 oldboy-bin.000001 -rw-r-----. 1 root root 35 8 August 11:14 oldboy-bin.index
(10). Now start restoring the contents of the previously existing library; Unzip the data backed up before. And write to the database.
[root@oldboy opt]# gzip -d bak_kk.sql.gz [root@oldboy opt]# mysql -uroot -poldboy123 <bak_kk.sql
(11). Set mysqlbinlog Oldboy bin 000001 >bin.sql copies the contents to bin SQL to prevent gross errors, VIM bin sql
To delete the boss, execute the command to delete the library. Otherwise, the backup will still be deleted, which means there is no backup. Finally, point to the data,
Just check the data again.
[root@oldboy opt]# mysqlbinlog oldboy-bin.000001 >bin.sql [root@oldboy opt]# vim bin.sql [root@oldboy opt]# mysql -uroot -poldboy123 <bin.sql
MariaDB [(none)]> select * from kk.student; +----+-----------+-----+------------------+ | id | name | age | dept | +----+-----------+-----+------------------+ | 6 | Old boy | 32 | python linux net | | 1 | Old boy | 32 | python linux net | | 5 | Qiao Feng | 32 | python linux net | +----+-----------+-----+------------------+
However, if I start binlog first, restart and wait to check whether there is mysqlbinlog Oldboy bin 00000 1, M has to go to the database and enter flush logs; You can see. At this time, if you insert data, back up data, and then perform the operation step by step, the file cannot be written in the last step. In this way, you can only delete the recovered database first, and then execute mysqlbinlog Oldboy bin 000001 >bin. SQL is better than allowing execution. The database deleted just now, the data written in by employees, and the data deleted by the boss will be retrieved. Here are some examples:
Through mysqlbinlog Oldboy bin 00000 2 you can view the statements of inserting new data and deleting the database;
drop database kk is found in the statement.
ode=0 use `kk`/*!*/; SET TIMESTAMP=1628399860/*!*/; insert into student values(9,'New content',32,'python linux net sec') /*!*/; # at 442 #210808 13:17:40 server id 1 end_log_pos 469 Xid = 6 COMMIT/*!*/; # at 469 #210808 13:18:26 server id 1 end_log_pos 546 Query thread_id=2 exec_time=0 error_code=0 SET TIMESTAMP=1628399906/*!*/; drop database kk /*!*/; DELIMITER ; # End of log file ROLLBACK /* added by mysqlbinlog */; /*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/; /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/; [root@oldboy mariadb]#
A problem occurred in the last step, saying that the file is not allowed to be inserted and that the file already exists. At this time, execute the drop database reshibu in the database, and then enter MySQL - uroot - poldboy123 < bin SQL is allowed to write.
[root@oldboy opt]# mysql -uroot -poldboy123 <bin.sql
ERROR 1007 (HY000) at line 27: Can't create database 'kk'; database exists
This is because you first open the binlog log and perform the operation in mysqldump. Therefore, the file cannot be written at last. Therefore, first mysqldump and then binlog, which can write the data backed up by mysqldump and binlong data at the same time.