mysql差异备份
文章目录
开启MySQL服务器的二进制日志功能
[root@localhost ~]# vi /etc/my.cnf
[root@localhost ~]# cat /etc/my.cnf
#
# This group is read both both by the client and the server
# use it for options that affect everything
#
[client-server]
[mysqld]
basedir = /usr/local/mysql
datadir = /opt/data
socket = /tmp/mysql.sock
port = 3306
user = mysql
pid-file = /tmp/mysql.pid
skip-name-resolve
server-id=1
log-bin=mysql_bin
#
# include all files from the config directory
#
!includedir /etc/my.cnf.d
对数据库进行完全备份
mysql> select * from haha;
+----+-----------+------+
| id | name | age |
+----+-----------+------+
| 2 | jerry | 23 |
| 3 | wangqing | 25 |
| 4 | sean | 28 |
| 5 | zhangshan | 26 |
| 6 | zhangshan | 20 |
| 7 | lisi | NULL |
+----+-----------+------+
6 rows in set (0.00 sec)
[root@localhost ~]# mysqldump -uroot -phzy123 --single-transaction --flush-logs --master-data=2 --all-databases --delete-master-logs > all-20210506.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@localhost ~]# ls
all-20210506.sql
anaconda-ks.cfg
mysql57-community-release-el7-8.noarch.rpm
增加新内容
mysql> insert into haha values(1,'xixi',21),(4,'didi',10);;
Query OK, 2 rows affected (0.06 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> select * from haha;
+----+-----------+------+
| id | name | age |
+----+-----------+------+
| 2 | jerry | 23 |
| 3 | wangqing | 25 |
| 4 | sean | 28 |
| 5 | zhangshan | 26 |
| 6 | zhangshan | 20 |
| 7 | lisi | NULL |
| 1 | xixi | 21 |
| 4 | didi | 10 |
+----+-----------+------+
8 rows in set (0.00 sec)
模拟误删数据
[root@localhost ~]# mysql -uroot -phzy123 -e 'drop database hzy;'
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@localhost ~]# mysql -uroot -phzy123 -e 'show databases;'
mysql: [Warning] Using a password on the command line interface can be insecure.
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
刷新创建新的二进制日志
[root@localhost ~]# mysqladmin -uroot -phzy123 flush-logs
恢复完全备份
[root@localhost ~]# mysql -uroot -phzy123 < all-20210506.sql
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@localhost ~]# mysql -uroot -phzy123 -e 'show databases;'
mysql: [Warning] Using a password on the command line interface can be insecure.
+--------------------+
| Database |
+--------------------+
| hzy |
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
[root@localhost ~]# mysql -uroot -phzy123 -e 'show tables from hzy;'
mysql: [Warning] Using a password on the command line interface can be insecure.
+---------------+
| Tables_in_hzy |
+---------------+
| haha |
+---------------+
[root@localhost ~]# mysql -uroot -phzy123 -e 'select * from hzy.haha;'
mysql: [Warning] Using a password on the command line interface can be insecure.
+----+-----------+------+
| id | name | age |
+----+-----------+------+
| 2 | jerry | 23 |
| 3 | wangqing | 25 |
| 4 | sean | 28 |
| 5 | zhangshan | 26 |
| 6 | zhangshan | 20 |
| 7 | lisi | NULL |
+----+-----------+------+
恢复差异备份
mysql> show binlog events in 'mysql_bin.000001';
+------------------+-----+----------------+-----------+-------------+--------------------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+------------------+-----+----------------+-----------+-------------+--------------------------------------------------+
| mysql_bin.000001 | 4 | Format_desc | 1 | 125 | Server ver: 8.0.21, Binlog ver: 4 |
| mysql_bin.000001 | 125 | Previous_gtids | 1 | 156 | |
| mysql_bin.000001 | 156 | Anonymous_Gtid | 1 | 235 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql_bin.000001 | 235 | Query | 1 | 324 | BEGIN |
| mysql_bin.000001 | 324 | Query | 1 | 445 | use `hzy`; update haha set age = 30 where id = 3 |
| mysql_bin.000001 | 445 | Xid | 1 | 476 | COMMIT /* xid=16 */ |
+------------------+-----+----------------+-----------+-------------+--------------------------------------------------+
6 rows in set (0.00 sec)
mysqlbinlog --stop-position=445 mysql_bin.000001 |mysql -uroot -phzy123
-> ;
mysql> select * from haha;
+----+-----------+------+
| id | name | age |
+----+-----------+------+
| 1 | xixi | 21 |
| 4 | didi | 10 |
+----+-----------+------+
8 rows in set (0.00 sec)
破解密码
service mysqld stop //先停止mysql服务。
[root@localhost ~]# service mysqld stop
Redirecting to /bin/systemctl stop mysqld.service
然后打开mysql配置文件/etc/my.cnf.在【mysqld】下面添加一行代码：skip-grant-tables。这行代码意思就是跳过跳过授权表，即是可以跳过密码验证直接进入数据库。
[root@localhost ~]# vi /etc/my.cnf
[root@localhost ~]# cat /etc/my.cnf
#
# This group is read both both by the client and the server
# use it for options that affect everything
#
[client-server]
[mysqld]
basedir = /usr/local/mysql
datadir = /opt/data
socket = /tmp/mysql.sock
port = 3306
user = mysql
pid-file = /tmp/mysql.pid
skip-name-resolve
skip-grant-tables
server-id=1
log-bin=mysql_bin
binlog_format = Mixed
#
# include all files from the config directory
#
!includedir /etc/my.cnf.d
service mysqld restart //重启mysql数据库。
[root@localhost ~]# service mysqld restart
Redirecting to /bin/systemctl restart mysqld.service
[root@localhost ~]# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 8.0.21 Source distribution
Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>
进数据库后，
use mysql //选择mysql这个库，因为mysql的root密码存放在这个数据库里。
show tables //查看下mysql库里有哪些表，我们需要操作的用户名密码都在user表里。
desc user //查看下user表有哪些字段
mysql> use mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> show tables;
+---------------------------+
| Tables_in_mysql |
+---------------------------+
| columns_priv |
| component |
| db |
| default_roles |
| engine_cost |
| func |
| general_log |
| global_grants |
| gtid_executed |
| help_category |
| help_keyword |
| help_relation |
| help_topic |
| innodb_index_stats |
| innodb_table_stats |
| password_history |
| plugin |
| procs_priv |
| proxies_priv |
| role_edges |
| server_cost |
| servers |
| slave_master_info |
| slave_relay_log_info |
| slave_worker_info |
| slow_log |
| tables_priv |
| time_zone |
| time_zone_leap_second |
| time_zone_name |
| time_zone_transition |
| time_zone_transition_type |
| user |
+---------------------------+
33 rows in set (0.01 sec)
mysql> desc user
-> ;
+--------------------------+-----------------------------------+------+-----+-----------------------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------------------------+-----------------------------------+------+-----+-----------------------+-------+
| Host | char(255) | NO | PRI | | |
| User | char(32) | NO | PRI | | |
| Select_priv | enum('N','Y') | NO | | N | |
| Insert_priv | enum('N','Y') | NO | | N | |
| Update_priv | enum('N','Y') | NO | | N | |
| Delete_priv | enum('N','Y') | NO | | N | |
| Create_priv | enum('N','Y') | NO | | N | |
| Drop_priv | enum('N','Y') | NO | | N | |
| Reload_priv | enum('N','Y') | NO | | N | |
| Shutdown_priv | enum('N','Y') | NO | | N | |
| Process_priv | enum('N','Y') | NO | | N | |
| File_priv | enum('N','Y') | NO | | N | |
| Grant_priv | enum('N','Y') | NO | | N | |
| References_priv | enum('N','Y') | NO | | N | |
| Index_priv | enum('N','Y') | NO | | N | |
| Alter_priv | enum('N','Y') | NO | | N | |
| Show_db_priv | enum('N','Y') | NO | | N | |
| Super_priv | enum('N','Y') | NO | | N | |
| Create_tmp_table_priv | enum('N','Y') | NO | | N | |
| Lock_tables_priv | enum('N','Y') | NO | | N | |
| Execute_priv | enum('N','Y') | NO | | N | |
| Repl_slave_priv | enum('N','Y') | NO | | N | |
| Repl_client_priv | enum('N','Y') | NO | | N | |
| Create_view_priv | enum('N','Y') | NO | | N | |
| Show_view_priv | enum('N','Y') | NO | | N | |
| Create_routine_priv | enum('N','Y') | NO | | N | |
| Alter_routine_priv | enum('N','Y') | NO | | N | |
| Create_user_priv | enum('N','Y') | NO | | N | |
| Event_priv | enum('N','Y') | NO | | N | |
| Trigger_priv | enum('N','Y') | NO | | N | |
| Create_tablespace_priv | enum('N','Y') | NO | | N | |
| ssl_type | enum('','ANY','X509','SPECIFIED') | NO | | | |
| ssl_cipher | blob | NO | | NULL | |
| x509_issuer | blob | NO | | NULL | |
| x509_subject | blob | NO | | NULL | |
| max_questions | int unsigned | NO | | 0 | |
| max_updates | int unsigned | NO | | 0 | |
| max_connections | int unsigned | NO | | 0 | |
| max_user_connections | int unsigned | NO | | 0 | |
| plugin | char(64) | NO | | caching_sha2_password | |
| authentication_string | text | YES | | NULL | |
| password_expired | enum('N','Y') | NO | | N | |
| password_last_changed | timestamp | YES | | NULL | |
| password_lifetime | smallint unsigned | YES | | NULL | |
| account_locked | enum('N','Y') | NO | | N | |
| Create_role_priv | enum('N','Y') | NO | | N | |
| Drop_role_priv | enum('N','Y') | NO | | N | |
| Password_reuse_history | smallint unsigned | YES | | NULL | |
| Password_reuse_time | smallint unsigned | YES | | NULL | |
| Password_require_current | enum('N','Y') | YES | | NULL | |
| User_attributes | json | YES | | NULL | |
+--------------------------+-----------------------------------+------+-----+-----------------------+-------+
51 rows in set (0.00 sec)
mysql>
更改root密码。
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY '123456';
Query OK, 0 rows affected (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
退出数据库，重新登录
然后再次进入配置文件vi /etc/my.cnf 把skip-grant-tables去掉。
mysql -uroot -p //回车输入刚刚更改的密码，就能进去了。
[root@localhost ~]# vi /etc/my.cnf
[root@localhost ~]# cat /etc/my.cnf
#
# This group is read both both by the client and the server
# use it for options that affect everything
#
[client-server]
[mysqld]
basedir = /usr/local/mysql
datadir = /opt/data
socket = /tmp/mysql.sock
port = 3306
user = mysql
pid-file = /tmp/mysql.pid
skip-name-resolve
server-id=1
log-bin=mysql_bin
binlog_format = Mixed
#
# include all files from the config directory
#
!includedir /etc/my.cnf.d
[root@localhost ~]# mysql -uroot -p123456
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.21 Source distribution
Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.