Mysql二進制安裝與備份的全過程記錄
更新時間：2022年01月27日 10:18:53 作者：??
這篇文章主要給大家介紹了關于Mysql二進制安裝與備份的相關資料,文中通過實例代碼介紹的非常詳細,對大家學習或者使用mysql具有一定的參考學習價值,需要的朋友可以參考下
Mysql的二進制安裝
下載安裝包
[root@localhost mysql]# wget https://downloads.mysql.com/archives/get/p/23/file/mysql-5.7.33-linux-glibc2.12-x86_64.tar.gz --2021-05-04 20:34:21-- https://downloads.mysql.com/archives/get/p/23/file/mysql-5.7.33-linux-glibc2.12-x86_64.tar.gz Resolving downloads.mysql.com (downloads.mysql.com)... 137.254.60.14 Connecting to downloads.mysql.com (downloads.mysql.com)|137.254.60.14|:443... connected. HTTP request sent, awaiting response... 302 Found Location: https://cdn.mysql.com/archives/mysql-5.7/mysql-5.7.33-linux-glibc2.12-x86_64.tar.gz [following] --2021-05-04 20:34:23-- https://cdn.mysql.com/archives/mysql-5.7/mysql-5.7.33-linux-glibc2.12-x86_64.tar.gz Resolving cdn.mysql.com (cdn.mysql.com)... 223.119.236.209 Connecting to cdn.mysql.com (cdn.mysql.com)|223.119.236.209|:443... connected. HTTP request sent, awaiting response... 200 OK Length: 661718255 (631M) [application/x-tar-gz] Saving to: ‘mysql-5.7.33-linux-glibc2.12-x86_64.tar.gz' mysql-5.7.33-linux- 100%[================>] 631.06M 11.3MB/s in 57s 2021-05-04 20:35:21 (11.0 MB/s) - ‘mysql-5.7.33-linux-glibc2.12-x86_64.tar.gz' saved [661718255/661718255]
創(chuàng)建用戶
[root@localhost mysql]# useradd -r -M -s /sbin/nologin mysql
解壓至指定目錄
[root@localhost mysql]# tar xf mysql-5.7.33-linux-glibc2.12-x86_64.tar.gz -C /usr/local
創(chuàng)建軟鏈接或者修改目錄名
[root@localhost local]# mv mysql-5.7.33-linux-glibc2.12-x86_64/ mysql [root@localhost local]# ls apache apr-util etc include lib64 mysql share apr bin games lib libexec sbin src
修改屬主和屬組
[root@localhost local]# chown -R mysql.mysql /usr/local/mysql/ [root@localhost local]# ll drwxr-xr-x. 9 mysql mysql 129 五月 4 20:40 mysql
設置環(huán)境變量（因為不是用yum裝的，找不到mysql程序）
[root@localhost mysql]# vim /etc/profile.d/mysql.sh export PATH=/usr/local/mysql/bin:$PATH [root@localhost mysql]# . /etc/profile.d/mysql.sh [root@localhost mysql]# which mysql /usr/local/mysql/bin/mysql
創(chuàng)建存放數(shù)據(jù)的目錄并修改屬主屬組
需要一個空間大的目錄，或者將目錄創(chuàng)建后將硬盤設備掛載在此目錄上
[root@localhost mysql]# mkdir /opt/mysql_data [root@localhost mysql]# chown -R mysql.mysql /opt/mysql_data/ [root@localhost mysql]# ll /opt drwxr-xr-x. 2 mysql mysql 6 五月 4 20:58 mysql_data
初始化并保存密碼
[root@localhost mysql]# /usr/local/mysql/bin/mysqld --initialize --user=mysql --datadir=/opt/mysql_data/ //初始化 控制mysql的用戶 數(shù)據(jù)存放目錄 2021-05-04T13:01:07.403961Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details). 2021-05-04T13:01:07.683107Z 0 [Warning] InnoDB: New log files created, LSN=45790 2021-05-04T13:01:07.739366Z 0 [Warning] InnoDB: Creating foreign key constraint system tables. 2021-05-04T13:01:07.746720Z 0 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: caa21b8a-acd8-11eb-b7ab-000c294bb269. 2021-05-04T13:01:07.747895Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened. 2021-05-04T13:01:09.096727Z 0 [Warning] CA certificate ca.pem is self signed. 2021-05-04T13:01:09.485357Z 1 [Note] A temporary password is generated for root@localhost: q_UG8?3sa/l% [root@localhost mysql]# vim password q_UG8?3sa/l%
寫配置文件
[root@localhost mysql]# vim /etc/my.cnf [mysqld] basedir = /usr/local/mysql //程序位置 datadir = /opt/mysql_data //數(shù)據(jù)存放位置 socket = /tmp/mysql.sock //文件套接字位置 port = 3306 //端口 pid-file = /opt/mysql_data/mysql.pid //進程文件位置 user = mysql //用戶 skip-name-resolve //跳過域名解析，即直接在內(nèi)網(wǎng)使用ip連接數(shù)據(jù)庫
配置啟動腳本和開機自啟
[root@localhost ~]# cp -a /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld
[root@localhost ~]# sed -ri 's#^(basedir=).*#\1/usr/local/mysql#g' /etc/init.d/mysqld
[root@localhost ~]# sed -ri 's#^(datadir=).*#\1/opt/mysql_data#g' /etc/init.d/mysqld
[root@localhost mysql]# head -47 /etc/init.d/mysqld |tail -2
basedir=/usr/local/mysql
datadir=/opt/mysql_data
[root@localhost mysql]# service mysqld start
Starting MySQL.Logging to '/opt/data/localhost.localdomain.err'.
SUCCESS!
[root@localhost mysql]# chkconfig mysqld on
[root@localhost mysql]# chkconfig --list
Note: This output shows SysV services only and does not include native
systemd services. SysV configuration data might be overridden by native
systemd configuration.
If you want to list systemd services use 'systemctl list-unit-files'.
To see services enabled on particular target use
'systemctl list-dependencies [target]'.
mysqld 0:off 1:off 2:on 3:on 4:on 5:on 6:off
[root@localhost mysql]# ss -anlt
State Recv-Q Send-Q Local Address:Port Peer Address:Port Process
LISTEN 0 128 0.0.0.0:22 0.0.0.0:*
LISTEN 0 128 *:443 *:*
LISTEN 0 80 *:3306 *:*
LISTEN 0 128 *:80 *:*
LISTEN 0 128 [::]:22 [::]:*
頭文件和庫文件配置
[root@localhost mysql]# ln -s /usr/local/mysql/include /usr/include/mysql [root@localhost mysql]# vim /etc/ld.so.conf.d/mysql.conf /usr/local/mysql/lib [root@localhost mysql]# ldconfig //重新讀取配置文件
啟動并設置密碼
[root@localhost local]# mysql -uroot -p mysql: error while loading shared libraries: libncurses.so.5: cannot open shared object file: No such file or directory //缺少包 [root@localhost local]# dnf provides libncurses.so.5 //查看包所依賴的類庫 Warning: failed loading '/etc/yum.repos.d/mssql-server.repo', skipping. ncurses-compat-libs-6.1-7.20180224.el8.i686 : Ncurses compatibility libraries Repo : baseos Matched from: Provide : libncurses.so.5 [root@localhost local]# dnf -y install ncurses-compat-libs [root@localhost local]# cat password /sdjtceDy7F7 [root@localhost local]# mysql -uroot -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 2 Server version: 5.7.33 Copyright (c) 2000, 2021, Oracle and/or its affiliates. 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>
Mysql的配置文件
mysql的配置文件為/etc/my.cnf
配置文件查找順序：若在多個配置文件中均有設定，則最后找到的配置文件中的配置生效
/etc/my.cnf --> /etc/mysql/my.cnf --> --default-extra-file=/PATH/TO/CONF_FILE --> ~/.my.cnf
| 參數(shù) | 說明 |
|---|---|
| port = 3306 | 設置監(jiān)聽端口，默認3306 |
| socket = /tmp/mysql.sock | 指定套接字文件位置 |
| basedir = /usr/local/mysql | 指定MySQL的安裝路徑 |
| datadir = /data/mysql | 指定MySQL的數(shù)據(jù)存放路徑 |
| pid-file = /data/mysql/mysql.pid | 指定進程ID文件存放路徑 |
| user = mysql | 指定MySQL以什么用戶的身份提供服務 |
| skip-name-resolve | 禁止MySQL對外部連接進行DNS解析 使用這一選項可以消除MySQL進行DNS解析的時間 若開啟該選項，則所有遠程主機連接授權都要使用IP地址方 式否則MySQL將無法正常處理連接請求 |
備份與恢復
數(shù)據(jù)庫常用備份方案
數(shù)據(jù)庫備份方案：
- 全量備份
- 增量備份
- 差異備份
| 備份方案 | 特點 |
|---|---|
| 全量備份 | 全量備份就是指對某一個時間點上的所有數(shù)據(jù)或應用進行的一個完全拷貝 數(shù)據(jù)恢復快，備份時間長 |
| 增量備份 | 增量備份是指在一次全備份或上一次增量備份后，以后每次的備份只需備份 與前一次相比增加和者被修改的文件。這就意味著，第一次增量備份的對象 是進行全備后所產(chǎn)生的增加和修改的文件 第二次增量備份的對象是進行第一次增量 備份后所產(chǎn)生的增加和修改的文件，如此類推 沒有重復的備份數(shù)據(jù) 備份時間短 恢復數(shù)據(jù)時必須按一定的順序進行 |
| 差異備份 | 備份上一次的完全備份后發(fā)生變化的所有文件 差異備份是指在一次全備份后到進行差異備份的這段時間內(nèi) 對那些增加或者修改文件的備份。在進行恢復時 我們只需對第一次全量備份和最后一次差異備份進行恢復 |
mysql備份工具mysqldump
mysqldump 語法：
??? ?mysqldump [OPTIONS] database [tables ...]?? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ?//備份數(shù)據(jù)表 ? ? mysqldump [OPTIONS] --all-databases [OPTIONS]?? ??? ??? ??? ??? ??? ??? ??? ??? ?//備份全部數(shù)據(jù)庫 ? ? mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3...]?? ??? ??? ?//備份某個數(shù)據(jù)庫 ?? ?-uUSERNAME ? ? ?//指定數(shù)據(jù)庫用戶名 ? ? -hHOST ? ? ? ? ?//指定服務器主機，請使用ip地址 ? ? -pPASSWORD ? ? ?//指定數(shù)據(jù)庫用戶的密碼 ? ? -P# ? ? ? ? ? ? //指定數(shù)據(jù)庫監(jiān)聽的端口，這里的#需用實際的端口號代替
備份所有數(shù)據(jù)庫
[root@localhost ~]# mysqldump -uroot -p --all-databases > /mysql_dump/all_20210504.sql Enter password: [root@localhost ~]# ls /mysql_dump/ all_20210504.sql
備份某個數(shù)據(jù)庫
[root@localhost ~]# mysqldump -uroot -p --databases hanao > /mysql_dump/hanao_db_20210504.sql Enter password: [root@localhost ~]# ls /mysql_dump/ all_20210504.sql hanao_db_20210504.sql
備份某個數(shù)據(jù)表
[root@localhost ~]# mysqldump -uroot -p hanao student > /mysql_dump/student_table_20210504.sql Enter password: [root@localhost ~]# ls /mysql_dump/ all_20210504.sql hanao_db_20210504.sql student_table_20210504.sql
mysql全備數(shù)據(jù)恢復
恢復數(shù)據(jù)表
mysql> use hanao 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> drop table student; Query OK, 0 rows affected (0.01 sec) mysql> show tables; Empty set (0.00 sec) [root@localhost ~]# cd /mysql_dump/ [root@localhost mysql_dump]# mysql -uroot -pHa153624. mysql> use hanao; Database changed mysql> source student_table_20210504.sql Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) mysql> show tables; +-----------------+ | Tables_in_hanao | +-----------------+ | student | +-----------------+ 1 row in set (0.00 sec)
恢復數(shù)據(jù)庫
mysql> drop database hanao; Query OK, 1 row affected (0.00 sec) [root@localhost mysql_dump]# mysql -uroot -pHa153624. < hanao_db_20210504.sql mysql: [Warning] Using a password on the command line interface can be insecure. [root@localhost mysql_dump]# mysql -uroot -pHa153624. -e 'show databases;' mysql: [Warning] Using a password on the command line interface can be insecure. +--------------------+ | Database | +--------------------+ | information_schema | | ha | | hanao | | mysql | | performance_schema | | sys | +--------------------+
恢復所有數(shù)據(jù)庫
mysql> drop database ha; Query OK, 1 row affected (0.01 sec) [root@localhost mysql_dump]# mysql -uroot -pHa153624. < all_20210504.sql mysql: [Warning] Using a password on the command line interface can be insecure. [root@localhost mysql_dump]# mysql -uroot -pHa153624. -e 'show databases;' mysql: [Warning] Using a password on the command line interface can be insecure. +--------------------+ | Database | +--------------------+ | information_schema | | ha | | hanao | | mysql | | performance_schema | | sys | +--------------------+
mysql差備（事務日志備份/binlog備份）
備份
開啟mysql服務二進制日志功能
root@localhost mysql_data]# vim /etc/my.cnf [mysqld] basedir = /usr/local/mysql datadir = /opt/mysql_data socket = /tmp/mysql.sock port = 3306 pid-file = /opt/mysql_data/mysql.pid user = mysql skip-name-resolve server-id=1 //設置服務器標識符 log-bin=mysql_bin //開啟二進制日志功能
重啟服務
[root@localhost mysql_data]# service mysqld restart Shutting down MySQL.. SUCCESS! Starting MySQL. SUCCESS!
先對數(shù)據(jù)庫進行全備
查看數(shù)據(jù)庫中的表和數(shù)據(jù)
[root@localhost /]# mysql -uroot -pHa153624... 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 4 Server version: 5.7.33-log MySQL Community Server (GPL) Copyright (c) 2000, 2021, Oracle and/or its affiliates. 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> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | hanao | | mysql | | performance_schema | | sys | +--------------------+ 5 rows in set (0.00 sec) mysql> use hanao; 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_hanao | +-----------------+ | student | +-----------------+ 1 row in set (0.00 sec) mysql> desc student; +-------+--------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------+--------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | name | varchar(100) | NO | | NULL | | | age | tinyint(4) | YES | | NULL | | +-------+--------------+------+-----+---------+----------------+ 3 rows in set (0.00 sec) mysql> select * from student; +----+-------------+------+ | id | name | age | +----+-------------+------+ | 1 | tom | 20 | | 2 | jerry | 23 | | 3 | wangqing | 25 | | 4 | sean | 28 | | 5 | zhangshan | 26 | | 6 | zhangshan | 20 | | 7 | lisi | NULL | | 8 | chengshuo | 10 | | 9 | wangwu | 3 | | 10 | qiuyi | 15 | | 11 | qiuxiaotian | 20 | +----+-------------+------+ 11 rows in set (0.00 sec)
對數(shù)據(jù)進行全備
[root@localhost mysql_dump]# mysqldump -uroot -pHa153624... --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 mysql_dump]# ll total 1720 -rw-r--r--. 1 root root 874762 五月 4 22:48 all_20210504.sql -rw-r--r--. 1 root root 873608 五月 6 18:19 all-20210506.sql -rw-r--r--. 1 root root 2250 五月 4 22:51 hanao_db_20210504.sql -rw-r--r--. 1 root root 2108 五月 4 22:52 student_table_20210504.sql
插入新數(shù)據(jù)
mysql> use hanao;
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> insert into student(name,age) value('hanao',22);
Query OK, 1 row affected (0.00 sec)
mysql> insert into student(name,age) value('hanao',21);
Query OK, 1 row affected (0.00 sec)
mysql> insert into student(name,age) value('tengjia',21);
Query OK, 1 row affected (0.00 sec)
mysql> select * from student;
+----+-------------+------+
| id | name | age |
+----+-------------+------+
| 1 | tom | 20 |
| 2 | jerry | 23 |
| 3 | wangqing | 25 |
| 4 | sean | 28 |
| 5 | zhangshan | 26 |
| 6 | zhangshan | 20 |
| 7 | lisi | NULL |
| 8 | chengshuo | 10 |
| 9 | wangwu | 3 |
| 10 | qiuyi | 15 |
| 11 | qiuxiaotian | 20 |
| 12 | hanao | 22 |
| 13 | hanao | 21 |
| 14 | tengjia | 21 |
+----+-------------+------+
14 rows in set (0.00 sec)
恢復備份
模擬誤刪數(shù)據(jù)
mysql> drop database hanao; Query OK, 1 row affected (0.00 sec) mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | sys | +--------------------+ 4 rows in set (0.00 sec)
刷新二進制日志文件
[root@localhost mysql_dump]# ll /opt/mysql_data/ total 123020 -rw-r-----. 1 mysql mysql 56 五月 4 22:01 auto.cnf -rw-------. 1 mysql mysql 1680 五月 4 22:01 ca-key.pem -rw-r--r--. 1 mysql mysql 1112 五月 4 22:01 ca.pem -rw-r--r--. 1 mysql mysql 1112 五月 4 22:01 client-cert.pem -rw-------. 1 mysql mysql 1680 五月 4 22:01 client-key.pem -rw-r-----. 1 mysql mysql 562 五月 6 18:06 ib_buffer_pool -rw-r-----. 1 mysql mysql 12582912 五月 6 18:24 ibdata1 -rw-r-----. 1 mysql mysql 50331648 五月 6 18:24 ib_logfile0 -rw-r-----. 1 mysql mysql 50331648 五月 4 22:01 ib_logfile1 -rw-r-----. 1 mysql mysql 12582912 五月 6 18:19 ibtmp1 -rw-r-----. 1 mysql mysql 58417 五月 6 18:06 localhost.localdomain.err drwxr-x---. 2 mysql mysql 4096 五月 4 23:03 mysql -rw-r-----. 1 mysql mysql 1129 五月 6 18:23 mysql_bin.000010 -rw-r-----. 1 mysql mysql 19 五月 6 18:19 mysql_bin.index -rw-r-----. 1 mysql mysql 6 五月 6 18:06 mysql.pid drwxr-x---. 2 mysql mysql 8192 五月 4 22:01 performance_schema -rw-------. 1 mysql mysql 1680 五月 4 22:01 private_key.pem -rw-r--r--. 1 mysql mysql 452 五月 4 22:01 public_key.pem -rw-r--r--. 1 mysql mysql 1112 五月 4 22:01 server-cert.pem -rw-------. 1 mysql mysql 1676 五月 4 22:01 server-key.pem drwxr-x---. 2 mysql mysql 8192 五月 4 22:01 sys [root@localhost mysql_dump]# mysqladmin -uroot -pHa153624... flush-logs mysqladmin: [Warning] Using a password on the command line interface can be insecure. [root@localhost mysql_dump]# ll /opt/mysql_data/ total 123024 -rw-r-----. 1 mysql mysql 56 五月 4 22:01 auto.cnf -rw-------. 1 mysql mysql 1680 五月 4 22:01 ca-key.pem -rw-r--r--. 1 mysql mysql 1112 五月 4 22:01 ca.pem -rw-r--r--. 1 mysql mysql 1112 五月 4 22:01 client-cert.pem -rw-------. 1 mysql mysql 1680 五月 4 22:01 client-key.pem -rw-r-----. 1 mysql mysql 562 五月 6 18:06 ib_buffer_pool -rw-r-----. 1 mysql mysql 12582912 五月 6 18:24 ibdata1 -rw-r-----. 1 mysql mysql 50331648 五月 6 18:24 ib_logfile0 -rw-r-----. 1 mysql mysql 50331648 五月 4 22:01 ib_logfile1 -rw-r-----. 1 mysql mysql 12582912 五月 6 18:19 ibtmp1 -rw-r-----. 1 mysql mysql 58417 五月 6 18:06 localhost.localdomain.err drwxr-x---. 2 mysql mysql 4096 五月 4 23:03 mysql -rw-r-----. 1 mysql mysql 1176 五月 6 18:24 mysql_bin.000010 -rw-r-----. 1 mysql mysql 154 五月 6 18:24 mysql_bin.000011 -rw-r-----. 1 mysql mysql 38 五月 6 18:24 mysql_bin.index -rw-r-----. 1 mysql mysql 6 五月 6 18:06 mysql.pid drwxr-x---. 2 mysql mysql 8192 五月 4 22:01 performance_schema -rw-------. 1 mysql mysql 1680 五月 4 22:01 private_key.pem -rw-r--r--. 1 mysql mysql 452 五月 4 22:01 public_key.pem -rw-r--r--. 1 mysql mysql 1112 五月 4 22:01 server-cert.pem -rw-------. 1 mysql mysql 1676 五月 4 22:01 server-key.pem drwxr-x---. 2 mysql mysql 8192 五月 4 22:01 sys [root@localhost mysql_dump]# cat /opt/mysql_data/mysql_bin.index ./mysql_bin.000010 ./mysql_bin.000011
查看恢復的全備的數(shù)據(jù)
數(shù)據(jù)中沒有后面新增的數(shù)據(jù)
[root@localhost mysql_dump]# mysql -uroot -pHa153624... < all-20210506.sql mysql: [Warning] Using a password on the command line interface can be insecure. [root@localhost mysql_dump]# mysql -uroot -pHa153624... -e 'show databases;' mysql: [Warning] Using a password on the command line interface can be insecure. +--------------------+ | Database | +--------------------+ | information_schema | | hanao | | mysql | | performance_schema | | sys | +--------------------+ [root@localhost mysql_dump]# mysql -uroot -pHa153624... -e 'select * from hanao.student;' mysql: [Warning] Using a password on the command line interface can be insecure. +----+-------------+------+ | id | name | age | +----+-------------+------+ | 1 | tom | 20 | | 2 | jerry | 23 | | 3 | wangqing | 25 | | 4 | sean | 28 | | 5 | zhangshan | 26 | | 6 | zhangshan | 20 | | 7 | lisi | NULL | | 8 | chengshuo | 10 | | 9 | wangwu | 3 | | 10 | qiuyi | 15 | | 11 | qiuxiaotian | 20 | +----+-------------+------+
恢復差異備份
查看誤刪數(shù)據(jù)庫的操作在什么地方
可以看到drop命令開始之前的Pos是1034，所以我們用1034恢復到數(shù)據(jù)刪除之前
[root@localhost mysql_dump]# mysql -uroot -pHa153624... mysql> show binlog events in 'mysql_bin.000010'; +------------------+------+----------------+-----------+-------------+---------------------------------------+ | Log_name | Pos | Event_type | Server_id | End_log_pos | Info | +------------------+------+----------------+-----------+-------------+---------------------------------------+ | mysql_bin.000010 | 4 | Format_desc | 1 | 123 | Server ver: 5.7.33-log, Binlog ver: 4 | | mysql_bin.000010 | 123 | Previous_gtids | 1 | 154 | | | mysql_bin.000010 | 154 | Anonymous_Gtid | 1 | 219 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' | | mysql_bin.000010 | 219 | Query | 1 | 292 | BEGIN | | mysql_bin.000010 | 292 | Table_map | 1 | 347 | table_id: 133 (hanao.student) | | mysql_bin.000010 | 347 | Write_rows | 1 | 394 | table_id: 133 flags: STMT_END_F | | mysql_bin.000010 | 394 | Xid | 1 | 425 | COMMIT /* xid=518 */ | | mysql_bin.000010 | 425 | Anonymous_Gtid | 1 | 490 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' | | mysql_bin.000010 | 490 | Query | 1 | 563 | BEGIN | | mysql_bin.000010 | 563 | Table_map | 1 | 618 | table_id: 133 (hanao.student) | | mysql_bin.000010 | 618 | Write_rows | 1 | 665 | table_id: 133 flags: STMT_END_F | | mysql_bin.000010 | 665 | Xid | 1 | 696 | COMMIT /* xid=519 */ | | mysql_bin.000010 | 696 | Anonymous_Gtid | 1 | 761 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' | | mysql_bin.000010 | 761 | Query | 1 | 834 | BEGIN | | mysql_bin.000010 | 834 | Table_map | 1 | 889 | table_id: 133 (hanao.student) | | mysql_bin.000010 | 889 | Write_rows | 1 | 938 | table_id: 133 flags: STMT_END_F | | mysql_bin.000010 | 938 | Xid | 1 | 969 | COMMIT /* xid=520 */ | | mysql_bin.000010 | 969 | Anonymous_Gtid | 1 | 1034 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' | | mysql_bin.000010 | 1034 | Query | 1 | 1129 | drop database hanao | | mysql_bin.000010 | 1129 | Rotate | 1 | 1176 | mysql_bin.000011;pos=4 | +------------------+------+----------------+-----------+-------------+---------------------------------------+ 20 rows in set (0.00 sec)
使用mysqlbinlog恢復差異備份
可以看到之前新增的數(shù)據(jù)也恢復回來了
[root@localhost mysql_dump]# mysqlbinlog --stop-position=1034 /opt/mysql_data/mysql_bin.000010 |mysql -uroot -pHa153624... mysql: [Warning] Using a password on the command line interface can be insecure. [root@localhost mysql_dump]# mysql -uroot -pHa153624... -e 'select * from hanao.student;' mysql: [Warning] Using a password on the command line interface can be insecure. +----+-------------+------+ | id | name | age | +----+-------------+------+ | 1 | tom | 20 | | 2 | jerry | 23 | | 3 | wangqing | 25 | | 4 | sean | 28 | | 5 | zhangshan | 26 | | 6 | zhangshan | 20 | | 7 | lisi | NULL | | 8 | chengshuo | 10 | | 9 | wangwu | 3 | | 10 | qiuyi | 15 | | 11 | qiuxiaotian | 20 | | 12 | hanao | 22 | | 13 | hanao | 21 | | 14 | tengjia | 21 | +----+-------------+------+
總結
到此這篇關于Mysql二進制安裝與備份的文章就介紹到這了,更多相關Mysql二進制安裝與備份內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關文章希望大家以后多多支持腳本之家！
您可能感興趣的文章:
- Linux下MySQL5.7.18二進制包安裝教程（無默認配置文件my_default.cnf）
- CentOS6.5 上部署 MySQL5.7.17 二進制安裝以及多實例配置
- Mysql5.7.18版本（二進制包安裝）自定義安裝路徑教程詳解
- Centos 7 安裝mysql5.7.24二進制 版本的方法及解決辦法
- Centos7.5安裝mysql5.7.24二進制包方式部署
- CentOS 7 中以命令行方式安裝 MySQL 5.7.11 for Linux Generic 二進制版本教程詳解
- linux二進制通用包安裝mysql5.6.20教程
- ubuntu系統(tǒng)中安裝mysql5.6(通過二進制)
- mysql 5.7.19 二進制最新安裝
- Linux下mysql5.6.24（二進制）自動安裝腳本
相關文章
本文主要介紹了mysql查詢結果實現(xiàn)多列拼接查詢，文中通過示例代碼介紹的非常詳細，對大家的學習或者工作具有一定的參考學習價值，需要的朋友們下面隨著小編來一起學習學習吧2022-04-04
這篇文章主要介紹了MySQL之FIND_IN_SET()的用法及說明，具有很好的參考價值，希望對大家有所幫助。如有錯誤或未考慮完全的地方，望不吝賜教2023-01-01
在關系數(shù)據(jù)庫中,索引是一種單獨的、物理的數(shù)對數(shù)據(jù)庫表中一列或多列的值進行排序的一種存儲結構,下面這篇文章主要給大家介紹了關于Mysql索引(index)的相關資料,文中通過實例代碼介紹的非常詳細,需要的朋友可以參考下2022-08-08
這篇文章主要介紹了JSP連接MySQL數(shù)據(jù)庫詳細步驟，文章內(nèi)容詳細全面，且通過實例進行講解，容易理解，需要的朋友可以參考下2023-01-01
本文帶領大家一起探討mysql分頁性能，需要的朋友一起看看吧2017-10-10
今天郁悶死了，在LINUX下調(diào)一個程序老說找不到表，但是我明明是建了表的，在MYSQL的命令行下也可以查到，為什么程序就找不到表呢?2014-04-04
Ubuntu下取消MySQL數(shù)據(jù)庫本機綁定限制方法
在Ubuntu系統(tǒng)中，添加了MySQL賬戶，賦予了數(shù)據(jù)庫完全操作權限，并且允許數(shù)據(jù)庫從外部鏈接 但是，還是無法遠程訪問MySQL數(shù)據(jù)庫2013-06-06
這篇文章主要介紹了哪些情況會導致MySQL索引失效，文章圍繞主題展開詳細的內(nèi)容介紹，具有一定的參考價值，需要的朋友可以參考一下2022-07-07
解決MySQL主從數(shù)據(jù)庫沒有同步的兩種方法
這篇文章主要介紹了解決MySQL主從數(shù)據(jù)庫沒有同步的兩種方法,需要的朋友可以參考下面文章內(nèi)容2021-09-09
mysql千萬級數(shù)據(jù)量根據(jù)索引優(yōu)化查詢速度的實現(xiàn)
這篇文章主要介紹了mysql千萬級數(shù)據(jù)量根據(jù)索引優(yōu)化查詢速度的實現(xiàn)，文中通過示例代碼介紹的非常詳細，對大家的學習或者工作具有一定的參考學習價值，需要的朋友們下面隨著小編來一起學習學習吧2021-03-03