mysql进阶

最新推荐文章于 2024-05-07 14:47:58 发布
最新推荐文章于 2024-05-07 14:47:58 发布
阅读量336 收藏 1
点赞数 1
文章标签： mysql linux 数据库 1024程序员节
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/yi_qingjun/article/details/109225502
版权

文章目录

1. 二进制格式mysql安装

  1. 进入官网下载软件包(下载第一个或者第三个)

  • 此种方式安装不能与yum方式安装共存（须恢复快照）

  • 官网
    在这里插入图片描述

  1. 将包传到/usr/src下面
    在这里插入图片描述
此时该目录下就有一个包
[root@localhost ~]# cd /usr/src/
[root@localhost src]# ls
debug    mysql-5.7.31-linux-glibc2.12-x86_64.tar.gz
kernels
[root@localhost src]# du -sh mysql-5.7.31-linux-glibc2.12-x86_64.tar.gz   读取该包大小
360M	mysql-5.7.31-linux-glibc2.12-x86_64.tar.gz
  1. 创建系统用户和组
该用户不能登陆
[root@localhost src]# useradd -r -M -s /sbin/nologin mysql
[root@localhost src]# id mysql
uid=994(mysql) gid=991(mysql) groups=991(mysql)
  1. 解压软件至/usr/local/
[root@localhost src]# tar xf mysql-5.7.31-linux-glibc2.12-x86_64.tar.gz -C /usr/local/
[root@localhost ~]# ls /usr/local/
bin      lib                                  sbin
etc      lib64                                share
games    libexec                              src
include  mysql-5.7.31-linux-glibc2.12-x86_64

做一个软连接（方便以后用mysql进入）
[root@localhost local]# ln -s mysql-5.7.31-linux-glibc2.12-x86_64/ mysql
[root@localhost local]# ll
total 0
drwxr-xr-x. 2 root root    6 Aug 12  2018 bin
drwxr-xr-x. 2 root root    6 Aug 12  2018 etc
drwxr-xr-x. 2 root root    6 Aug 12  2018 games
drwxr-xr-x. 2 root root    6 Aug 12  2018 include
drwxr-xr-x. 2 root root    6 Aug 12  2018 lib
drwxr-xr-x. 2 root root    6 Aug 12  2018 lib64
drwxr-xr-x. 2 root root    6 Aug 12  2018 libexec
lrwxrwxrwx. 1 root root   36 Oct 22 17:16 mysql -> mysql-5.7.31-linux-glibc2.12-x86_64/
drwxr-xr-x. 9 7161 31415 129 Jun  2 21:11 mysql-5.7.31-linux-glibc2.12-x86_64
drwxr-xr-x. 2 root root    6 Aug 12  2018 sbin
drwxr-xr-x. 5 root root   49 Aug 26 19:16 share
drwxr-xr-x. 2 root root    6 Aug 12  2018 src

将所有者和所有组改成mysql（之前是root）
[root@localhost local]# chown -R mysql.mysql mysql*
[root@localhost local]# ll
total 0
drwxr-xr-x. 2 root  root    6 Aug 12  2018 bin
drwxr-xr-x. 2 root  root    6 Aug 12  2018 etc
drwxr-xr-x. 2 root  root    6 Aug 12  2018 games
drwxr-xr-x. 2 root  root    6 Aug 12  2018 include
drwxr-xr-x. 2 root  root    6 Aug 12  2018 lib
drwxr-xr-x. 2 root  root    6 Aug 12  2018 lib64
drwxr-xr-x. 2 root  root    6 Aug 12  2018 libexec
lrwxrwxrwx. 1 mysql mysql  36 Oct 22 17:16 mysql -> mysql-5.7.31-linux-glibc2.12-x86_64/
drwxr-xr-x. 9 mysql mysql 129 Jun  2 21:11 mysql-5.7.31-linux-glibc2.12-x86_64
drwxr-xr-x. 2 root  root    6 Aug 12  2018 sbin
drwxr-xr-x. 5 root  root   49 Aug 26 19:16 share
drwxr-xr-x. 2 root  root    6 Aug 12  2018 src
  1. 添加一个环境变量
[root@localhost local]# vi /etc/profile.d/mysql.sh

export PATH=/usr/local/mysql/bin:$PATH
[root@localhost local]# source /etc/profile.d/mysql.sh      读取
[root@localhost local]# echo $PATH
/usr/local/mysql/bin:/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/root/bin
[root@localhost local]# which mysql     此时mysql可以tab出来（没做此步之前tab不出来）
/usr/local/mysql/bin/mysql
  1. 建立数据存放目录
[root@localhost ~]# mkdir /opt/data
[root@localhost ~]# chown -R mysql.mysql /opt/data
[root@localhost ~]# ll /opt/
total 0
drwxr-xr-x. 2 mysql mysql 6 Oct 22 17:27 data
[root@localhost ~]# ls /opt/data/
[root@localhost ~]#                     此时该目录下没有任何东西
  1. 初始化数据库
初始化之后最后面有个临时密码，这个密码是随机的每个人都不一样，要记下来，一会儿登陆要用
[root@localhost ~]# mysqld --initialize --user=mysql --datadir=/opt/data/   
2020-10-22T09:33:02.959591Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2020-10-22T09:33:03.151087Z 0 [Warning] InnoDB: New log files created, LSN=45790
2020-10-22T09:33:03.188005Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2020-10-22T09:33:03.242721Z 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: 95267a2f-1449-11eb-9a41-000c291636de.
2020-10-22T09:33:03.243368Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2020-10-22T09:33:03.869523Z 0 [Warning] CA certificate ca.pem is self signed.
2020-10-22T09:33:04.056379Z 1 [Note] A temporary password is generated for root@localhost: jbn=rgQ4k96S
把密码写到pass里取，方便记忆
[root@localhost ~]# echo 'jbn=rgQ4k96S' > pass
[root@localhost ~]# ls
anaconda-ks.cfg  pass
[root@localhost ~]# cat pass
jbn=rgQ4k96S
  1. 生成配置文件
配置文件里添加以下内容
[root@localhost ~]# vim /etc/my.cnf 

[mysqld]              #表示服务端，固定写法
basedir = /usr/local/mysql                    #安装目录
datadir = /opt/data                       #数据存放位置
socket = /tmp/mysql.sock                #套接字文件存放位置
port = 3306                                  #默认端口
pid-file = /opt/data/mysql.pid                          #进程文件存放位置
user = mysql                           #那个用户启动服务
skip-name-resolve                     #关闭域名解析域名连接，跳过名称解析
  1. 配置服务脚本
[root@localhost mysql]# cd support-files/
[root@localhost support-files]# ls
magic                mysql-log-rotate
mysqld_multi.server  mysql.server
[root@localhost support-files]# cp mysql.server /etc/init.d/mysqld
[root@localhost ~]# ll /etc/init.d/mysqld
-rwxr-xr-x. 1 root root 10576 Oct 22 18:29 /etc/init.d/mysqld
[root@localhost ~]# vim /etc/init.d/mysqld
basedir=/usr/local/mysql    添加这两行
datadir=/opt/data

10 启动服务

[root@localhost ~]# service mysqld start
Starting MySQL. SUCCESS! 
[root@localhost ~]# ss -antl
State  Recv-Q Send-Q Local Address:Port  Peer Address:Port                                                  
LISTEN 0      128          0.0.0.0:22         0.0.0.0:*                                                     
LISTEN 0      80                 *:3306             *:*                                                     
LISTEN 0      128             [::]:22            [::]:*
  1. 设置开机自动启动
[root@localhost ~]# 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]'.

[root@localhost ~]# chkconfig --add mysqld
[root@localhost ~]# 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 ~]# ps -ef|grep mysql
root       10501       1  0 18:40 pts/1    00:00:00 /bin/sh /usr/local/mysql/bin/mysqld_safe --datadir=/opt/data --pid-file=/opt/data/mysql.pid
mysql      10691   10501  0 18:40 pts/1    00:00:00 /usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql --datadir=/opt/data --plugin-dir=/usr/local/mysql/lib/plugin --user=mysql --log-error=localhost.localdomain.err --pid-file=/opt/data/mysql.pid --socket=/tmp/mysql.sock --port=3306
root       10751    1405  0 18:47 pts/1    00:00:00 grep --color=auto mysql
[root@localhost ~]# ss -antl
State  Recv-Q Send-Q Local Address:Port  Peer Address:Port                                                  
LISTEN 0      128          0.0.0.0:22         0.0.0.0:*                                                     
LISTEN 0      80                 *:3306             *:*                                                     
LISTEN 0      128             [::]:22            [::]:*
  1. 修改密码
使用whatprovides来查看安装什么包
[root@localhost ~]# yum whatprovides libncurses.so.5
Updating Subscription Management repositories.
Unable to read consumer identity
This system is not registered to Red Hat Subscription Management. You can use subscription-manager to register.
Last metadata expiration check: 0:37:05 ago on Thu 22 Oct 2020 06:22:54 PM CST.
ncurses-compat-libs-6.1-7.20180224.el8.i686 : Ncurses
     ...: compatibility libraries
Repo        : BaseOS
Matched from:
Provide    : libncurses.so.5

安装 ncurses-devel
[root@localhost ~]# yum -y -q install ncurses-devel
[root@localhost ~]# rpm -qa|grep ncurses
ncurses-base-6.1-7.20180224.el8.noarch
ncurses-c++-libs-6.1-7.20180224.el8.x86_64
ncurses-devel-6.1-7.20180224.el8.x86_64
ncurses-libs-6.1-7.20180224.el8.x86_64
ncurses-6.1-7.20180224.el8.x86_64
安装ncurses-compat-libs
[root@localhost ~]# yum -y install ncurses-compat-libs
[root@localhost ~]# rpm -qa|grep ncurses-compat-libs
ncurses-compat-libs-6.1-7.20180224.el8.x86_64
登陆
[root@localhost ~]# mysql -uroot -p'jbn=rgQ4k96S'
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 2
Server version: 5.7.31

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> 
登陆进去后设置密码
mysql> set password = password('maqiang123');
Query OK, 0 rows affected, 1 warning (0.00 sec)
用新密码可以连接进来
[root@localhost ~]# mysql -uroot -p'maqiang123'
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 3
Server version: 5.7.31 MySQL Community Server (GPL)

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>

2. mysql配置文件

  • mysql的配置文件为/etc/my.cnf

配置文件查找次序：若在多个配置文件中均有设定，则最后找到的最终生效

  • /etc/my.cnf --> /etc/mysql/my.cnf --> --default-extra-file=/PATH/TO/CONF_FILE --> ~/.my.cnf

设置免密登陆

在家目录里写一个这样的文件
[root@localhost ~]# vim .my.cnf 

[client]
user = root
password = maqiang123     之前设置的密码


直接免密登陆
[root@localhost ~]# mysql -uroot
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 5.7.31 MySQL Community Server (GPL)

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>

mysql常用配置文件参数：

参数说明
port = 3306设置监听端口
socket = /tmp/mysql.sock指定套接字文件位置
basedir = /usr/local/mysql指定MySQL的安装路径
datadir = /data/mysql指定MySQL的数据存放路径
pid-file = /data/mysql/mysql.pid指定进程ID文件存放路径
user = mysql指定MySQL以什么用户的身份提供服务
skip-name-resolve禁止MySQL对外部连接进行DNS解析
使用这一选项可以消除MySQL进行DNS解析的时间。
若开启该选项，则所有远程主机连接授权都要
使用IP地址方式否则MySQL将无法正常处理连接请求

3. mysql数据库备份与恢复

3.1 数据库常用备份方案

数据库备份方案：

  • 全量备份
  • 增量备份
  • 差异备份
备份方案特点
全量备份全量备份就是指对某一个时间点上的所有数据或应用进行的一个完全拷贝。
数据恢复快。
备份时间长
增量备份增量备份是指在一次全量备份或上一次增量备份后，以后每次的备份只需备份
与前一次相比增加和者被修改的文件。这就意味着，第一次增量备份的对象
是进行全备后所产生的增加和修改的文件；第二次增量备份的对象是进行第一次增量备份后所产生的增加和修改的文件，如此类推。
没有重复的备份数据
备份时间短
恢复数据时必须按一定的顺序进行
差异备份备份上一次的完全备份后发生变化的所有文件。
差异备份是指在一次全备份后到进行差异备份的这段时间内
对那些增加或者修改文件的备份。在进行恢复时，我们只需对第一次全量备份和最后一次差异备份进行恢复。

3.2 mysql备份工具mysqldump

语法：

  • mysqldump [OPTIONS] database [tables …]
  • mysqldump [OPTIONS] --all-databases [OPTIONS]
  • mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3…]
  1. 恢复表（在里面恢复）
//常用的OPTIONS：
    -uUSERNAME      //指定数据库用户名
    -hHOST          //指定服务器主机，请使用ip地址
    -pPASSWORD      //指定数据库用户的密码
    -P#             //指定数据库监听的端口，这里的#需用实际的端口号代替，如-P3307
 

备份整个数据库(全备)
1. 把student数据写到student.sql文件里
[root@localhost ~]# mysqldump -uroot -pmaqiang123 maqiang student > student.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@localhost ~]# ls
anaconda-ks.cfg  pass  student.sql
 

2. 进入数据库删除student
[root@localhost ~]# mysql -uroot
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 5.7.31 MySQL Community Server (GPL)

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 maqiang;
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.00 sec)

mysql> show tables;
+-------------------+
| Tables_in_maqiang |
+-------------------+
| student1          |
+-------------------+
1 row in set (0.00 sec)


3. 读取student.sql
mysql> source student.sql;
Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected, 1 warning (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.01 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected, 1 warning (0.01 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)


4. 此时该表已被恢复
mysql> show tables;
+-------------------+
| Tables_in_maqiang |
+-------------------+
| student           |
| student1          |
+-------------------+
2 rows in set (0.00 sec)


5. 该表的内容已被恢复
mysql> select * from student;
+----+-------+-------+
| id | name  | score |
+----+-------+-------+
|  1 | tom   |    20 |
|  2 | jerry |    80 |
|  3 | zs    |    60 |
|  4 | lisi  |    90 |
+----+-------+-------+
4 rows in set (0.00 sec)
  1. 备份两张表
备份runtime和linux20200727这两个数据库，且会备份其中的表
[root@localhost ~]# mysqldump --databases runtime linux20200727 > multi_database-20201023.sql
[root@localhost ~]# ls
anaconda-ks.cfg              student-202010230940.sql
multi_database-20201023.sql  student-202010230942.sql
pass                         student.sql
  1. 恢复表（在外面恢复）
查看表
mysql> show tables from maqiang;
+-------------------+
| Tables_in_maqiang |
+-------------------+
| student           |
| student1          |
+-------------------+
2 rows in set (0.00 sec)
删除两张表
mysql> drop table maqiang.student;
Query OK, 0 rows affected (0.00 sec)
mysql> drop table maqiang.student1;
Query OK, 0 rows affected (0.01 sec)
mysql> show tables from maqiang;
Empty set (0.00 sec)
mysql> quit
Bye
将这两张表恢复到maqiang数据库里
[root@localhost ~]# mysql maqiang < student-202010230942.sql     恢复表时需要指定恢复到哪个库里
此时两张表已恢复
[root@localhost ~]# mysql -e 'show tables from maqiang;'
+-------------------+
| Tables_in_maqiang |
+-------------------+
| student           |
| student1          |
+-------------------+
两张表的内容也恢复了
[root@localhost ~]# mysql -e 'select * from maqiang.student;'
+----+-------+-------+
| id | name  | score |
+----+-------+-------+
|  1 | tom   |    20 |
|  2 | jerry |    80 |
|  3 | zs    |    60 |
|  4 | lisi  |    90 |
+----+-------+-------+
  1. 恢复库
先删除这两个库
[root@localhost ~]# mysql -e 'drop database runtime;'
[root@localhost ~]# mysql -e 'drop database linux20200727;'
[root@localhost ~]# mysql -e 'show databases;'
+--------------------+
| Database           |
+--------------------+
| information_schema |
| maqiang            |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
恢复库时不需要指定恢复到哪儿，但是恢复表时就需要指定恢复到哪个库里
[root@localhost ~]# mysql < multi_database-20201023.sql 
此时查看就有这两个库了
[root@localhost ~]# mysql -e 'show databases;'
+--------------------+
| Database           |
+--------------------+
| information_schema |
| linux20200727      |
| maqiang            |
| mysql              |
| performance_schema |
| runtime            |
| sys                |
+--------------------+

3.3 全量备份

  1. 全量备份数据库
将所有的数据库备份到该文件里
[root@localhost ~]# mysqldump --all-databases > all-202010231004.sql
[root@localhost ~]# ls
all-202010231004.sql  anaconda-ks.cfg
查看有哪些数据库
[root@localhost ~]# mysql -e 'show databases;'
+--------------------+
| Database           |
+--------------------+
| information_schema |
| linux20200727      |
| maqiang            |
| mysql              |
| performance_schema |
| runtime            |
| sys                |
+--------------------+
删除这三个数据库
[root@localhost ~]# mysql -e 'drop database maqiang;'
[root@localhost ~]# mysql -e 'drop database runtime;'
[root@localhost ~]# mysql -e 'drop database linux20200727;'
[root@localhost ~]# mysql -e 'show databases;'
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
恢复数据库（如果数据库都存在，也可以执行此步骤）
[root@localhost ~]# mysql < all-202010231004.sql 
此时那三个数据库已被恢复
[root@localhost ~]# mysql -e 'show databases;'
+--------------------+
| Database           |
+--------------------+
| information_schema |
| linux20200727      |
| maqiang            |
| mysql              |
| performance_schema |
| runtime            |
| sys                |
+--------------------+
  1. 如果全量备份后，在数据库里更改了数据那么再次恢复时是恢复到备份之前的内容
    示例：
1. 在此之前我进行了全量备份，此时的jerry的score为80
mysql> select * from student;
+----+-------+-------+
| id | name  | score |
+----+-------+-------+
|  1 | tom   |    20 |
|  2 | jerry |    80 |
|  3 | zs    |    60 |
|  4 | lisi  |    90 |
+----+-------+-------+
4 rows in set (0.00 sec)

2. 把jerry的score改为100
mysql> update student set score = 100 where id = 2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from student;
+----+-------+-------+
| id | name  | score |
+----+-------+-------+
|  1 | tom   |    20 |
|  2 | jerry |   100 |
|  3 | zs    |    60 |
|  4 | lisi  |    90 |
+----+-------+-------+
4 rows in set (0.00 sec)

mysql> quit
Bye

3. 恢复数据
[root@localhost ~]# mysql < all-202010231004.sql 

4. 此时查看jerry的score为80 ,并非为100
[root@localhost ~]# mysql -e 'select * from maqiang.student;'
+----+-------+-------+
| id | name  | score |
+----+-------+-------+
|  1 | tom   |    20 |
|  2 | jerry |    80 |
|  3 | zs    |    60 |
|  4 | lisi  |    90 |
+----+-------+-------+
  1. 修改数据后再全量备份，那么恢复时是恢复到之前的内容
    示例：
7. 将jerry的score改成100
mysql> update student set score = 100 where id = 2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from student;
+----+-------+-------+
| id | name  | score |
+----+-------+-------+
|  1 | tom   |    20 |
|  2 | jerry |   100 |
|  3 | zs    |    60 |
|  4 | lisi  |    90 |
+----+-------+-------+
4 rows in set (0.00 sec)

mysql> quit
Bye

2. 进行全量备份
[root@localhost ~]# mysqldump --all-databases > all-202010231005.sql 

3. 删除maqiang这个数据库
[root@localhost ~]# mysql -e 'drop database maqiang;'
[root@localhost ~]# mysql -e 'show databases;'
+--------------------+
| Database           |
+--------------------+
| information_schema |
| linux20200727      |
| mysql              |
| performance_schema |
| runtime            |
| sys                |
+--------------------+

4. 恢复
[root@localhost ~]# mysql < all-202010231005.sql 
[root@localhost ~]# mysql -e 'show databases;'
+--------------------+
| Database           |
+--------------------+
| information_schema |
| linux20200727      |
| maqiang            |
| mysql              |
| performance_schema |
| runtime            |
| sys                |
+--------------------+

5. 此时的jerry的score为100
[root@localhost ~]# mysql -e 'select * from maqiang.student;'
+----+-------+-------+
| id | name  | score |
+----+-------+-------+
|  1 | tom   |    20 |
|  2 | jerry |   100 |
|  3 | zs    |    60 |
|  4 | lisi  |    90 |
+----+-------+-------+

3.4 差异备份与恢复

3.4.1. mysql差异备份

开启MySQL服务器的二进制日志功能

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

[mysqld]
basedir = /usr/local/mysql
datadir = /opt/data
socket = /tmp/mysql.sock
port = 3306
pid-file = /opt/data/mysql.pid
user = mysql
skip-name-resolve

                                            添加以下两行内容
server-id = 10               设置服务器标识符
log-bin=mysql_bin                开启二进制日志功能

重启
[root@localhost ~]# service mysqld restart
Shutting down MySQL.. SUCCESS! 
Starting MySQL. SUCCESS! 
[root@localhost ~]# ss -antl
State  Recv-Q Send-Q Local Address:Port  Peer Address:Port                                                  
LISTEN 0      128          0.0.0.0:22         0.0.0.0:*                                                     
LISTEN 0      80                 *:3306             *:*                                                     
LISTEN 0      128             [::]:22            [::]:

对数据库进行完全备份

查看数据库
[root@localhost ~]# mysql -e 'show databases;'
+--------------------+
| Database           |
+--------------------+
| information_schema |
| linux20200727      |
| maqiang            |
| mysql              |
| performance_schema |
| runtime            |
| sys                |
+--------------------+

进行完全备份
[root@localhost ~]#  mysqldump -uroot -pmaqiang123 --single-transaction --flush-logs --master-data=2 --all-databases --delete-master-logs > all-2020.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@localhost ~]# ls
all-2020.sql  anaconda-ks.cfg



增
mysql> select * from student;
+----+-------+-------+
| id | name  | score |
+----+-------+-------+
|  1 | tom   |    20 |
|  2 | jerry |   100 |
|  3 | zs    |    60 |
|  4 | lisi  |    90 |
+----+-------+-------+
4 rows in set (0.00 sec)
mysql> insert student(name,score) values('wnagwu',87),(('qianliu',67);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from student;
+----+---------+-------+
| id | name    | score |
+----+---------+-------+
|  1 | tom     |    20 |
|  2 | jerry   |   100 |
|  3 | zs      |    60 |
|  4 | lisi    |    90 |
|  5 | wnagwu  |    87 |
|  6 | qianliu |    67 |
+----+---------+-------+
6 rows in set (0.00 sec)

改
mysql> update student set score = 98 where name = 'jerry';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from student;
+----+---------+-------+
| id | name    | score |
+----+---------+-------+
|  1 | tom     |    20 |
|  2 | jerry   |    98 |
|  3 | zs      |    60 |
|  4 | lisi    |    90 |
|  5 | wnagwu  |    87 |
|  6 | qianliu |    67 |
+----+---------+-------+
6 rows in set (0.00 sec)

3.4.2. mysql差异备份恢复

模拟误删数据

删除maqiang这个数据库
[root@localhost ~]# mysql -e 'drop database maqiang;'
[root@localhost ~]# mysql -e 'show databases;'
+--------------------+
| Database           |
+--------------------+
| information_schema |
| linux20200727      |
| mysql              |
| performance_schema |
| runtime            |
| sys                |
+--------------------+

刷新创建新的二进制日志

此时查看日志文件，记录了前面的所有操作
[root@localhost ~]# ll /opt/data
total 122992
-rw-r-----. 1 mysql mysql       56 Oct 22 17:33 auto.cnf
-rw-------. 1 mysql mysql     1676 Oct 22 17:33 ca-key.pem
-rw-r--r--. 1 mysql mysql     1112 Oct 22 17:33 ca.pem
-rw-r--r--. 1 mysql mysql     1112 Oct 22 17:33 client-cert.pem
-rw-------. 1 mysql mysql     1676 Oct 22 17:33 client-key.pem
-rw-r-----. 1 mysql mysql     1355 Oct 24 14:55 ib_buffer_pool
-rw-r-----. 1 mysql mysql 12582912 Oct 24 15:15 ibdata1
-rw-r-----. 1 mysql mysql 50331648 Oct 24 15:15 ib_logfile0
-rw-r-----. 1 mysql mysql 50331648 Oct 22 17:33 ib_logfile1
-rw-r-----. 1 mysql mysql 12582912 Oct 24 15:03 ibtmp1
drwxr-x---. 2 mysql mysql       58 Oct 24 14:40 linux20200727
-rw-r-----. 1 mysql mysql    32236 Oct 24 14:55 localhost.localdomain.err
drwxr-x---. 2 mysql mysql     4096 Oct 24 14:40 mysql
-rw-r-----. 1 mysql mysql      912 Oct 24 15:13 mysql_bin.000002
-rw-r-----. 1 mysql mysql       19 Oct 24 15:03 mysql_bin.index
-rw-r-----. 1 mysql mysql        5 Oct 24 14:55 mysql.pid
drwxr-x---. 2 mysql mysql     8192 Oct 22 17:33 performance_schema
-rw-------. 1 mysql mysql     1680 Oct 22 17:33 private_key.pem
-rw-r--r--. 1 mysql mysql      452 Oct 22 17:33 public_key.pem
drwxr-x---. 2 mysql mysql       52 Oct 24 14:40 runtime
-rw-r--r--. 1 mysql mysql     1112 Oct 22 17:33 server-cert.pem
-rw-------. 1 mysql mysql     1676 Oct 22 17:33 server-key.pem
drwxr-x---. 2 mysql mysql     8192 Oct 22 17:33 sys
[root@localhost ~]# cat /opt/data/mysql_bin.index                  这个mysql_bin.000002里面记录到了前面操作的删除maqiang这一步骤
./mysql_bin.000002


刷新创建新的二进制日志
[root@localhost ~]# mysqladmin -uroot -pmaqiang123 flush-logs
mysqladmin: [Warning] Using a password on the command line interface can be insecure.
[root@localhost ~]# ll /opt/data
total 122996
-rw-r-----. 1 mysql mysql       56 Oct 22 17:33 auto.cnf
-rw-------. 1 mysql mysql     1676 Oct 22 17:33 ca-key.pem
-rw-r--r--. 1 mysql mysql     1112 Oct 22 17:33 ca.pem
-rw-r--r--. 1 mysql mysql     1112 Oct 22 17:33 client-cert.pem
-rw-------. 1 mysql mysql     1676 Oct 22 17:33 client-key.pem
-rw-r-----. 1 mysql mysql     1355 Oct 24 14:55 ib_buffer_pool
-rw-r-----. 1 mysql mysql 12582912 Oct 24 15:15 ibdata1
-rw-r-----. 1 mysql mysql 50331648 Oct 24 15:15 ib_logfile0
-rw-r-----. 1 mysql mysql 50331648 Oct 22 17:33 ib_logfile1
-rw-r-----. 1 mysql mysql 12582912 Oct 24 15:03 ibtmp1
drwxr-x---. 2 mysql mysql       58 Oct 24 14:40 linux20200727
-rw-r-----. 1 mysql mysql    32236 Oct 24 14:55 localhost.localdomain.err
drwxr-x---. 2 mysql mysql     4096 Oct 24 14:40 mysql
-rw-r-----. 1 mysql mysql      959 Oct 24 15:21 mysql_bin.000002
-rw-r-----. 1 mysql mysql      154 Oct 24 15:21 mysql_bin.000003
-rw-r-----. 1 mysql mysql       38 Oct 24 15:21 mysql_bin.index
-rw-r-----. 1 mysql mysql        5 Oct 24 14:55 mysql.pid
drwxr-x---. 2 mysql mysql     8192 Oct 22 17:33 performance_schema
-rw-------. 1 mysql mysql     1680 Oct 22 17:33 private_key.pem
-rw-r--r--. 1 mysql mysql      452 Oct 22 17:33 public_key.pem
drwxr-x---. 2 mysql mysql       52 Oct 24 14:40 runtime
-rw-r--r--. 1 mysql mysql     1112 Oct 22 17:33 server-cert.pem
-rw-------. 1 mysql mysql     1676 Oct 22 17:33 server-key.pem
drwxr-x---. 2 mysql mysql     8192 Oct 22 17:33 sys
[root@localhost ~]# cat /opt/data/mysql_bin.index 
./mysql_bin.000002
./mysql_bin.000003                    此时生成了一个mysql_bin.000003,里面将记录后面的操作

恢复完全备份

恢复
[root@localhost ~]# mysql -uroot -pmaqiang123 < all-2020.sql 
mysql: [Warning] Using a password on the command line interface can be insecure.


查看数据库
[root@localhost ~]# mysql -e 'show databases;'
+--------------------+
| Database           |
+--------------------+
| information_schema |
| linux20200727      |
| maqiang            |
| mysql              |
| performance_schema |
| runtime            |
| sys                |
+--------------------+


查看表的内容（此时改表里并没有之前增加和修改的内容，是因为我是全量备份之后再进行增加和修改的，所以我恢复是恢复到增改之前的内容。此时若想要恢复到增改之后的内容就要用差量备份恢复，需要用到mysql_bin.000002，这里记录到了增改的操作）
[root@localhost ~]# mysql -e 'select * from maqiang.student;'
+----+-------+-------+
| id | name  | score |
+----+-------+-------+
|  1 | tom   |    20 |
|  2 | jerry |   100 |
|  3 | zs    |    60 |
|  4 | lisi  |    90 |
+----+-------+-------+

恢复差异备份

检查误删数据库的位置在什么地方(此时我能知道删除maqiang数据库之前是811,删除之后是912)
mysql> show binlog events in 'mysql_bin.000002';
+------------------+-----+----------------+-----------+-------------+---------------------------------------+
| Log_name         | Pos | Event_type     | Server_id | End_log_pos | Info                                  |
+------------------+-----+----------------+-----------+-------------+---------------------------------------+
| mysql_bin.000002 |   4 | Format_desc    |        10 |         123 | Server ver: 5.7.31-log, Binlog ver: 4 |
| mysql_bin.000002 | 123 | Previous_gtids |        10 |         154 |                                       |
| mysql_bin.000002 | 154 | Anonymous_Gtid |        10 |         219 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'  |
| mysql_bin.000002 | 219 | Query          |        10 |         294 | BEGIN                                 |
| mysql_bin.000002 | 294 | Table_map      |        10 |         352 | table_id: 124 (maqiang.student)       |
| mysql_bin.000002 | 352 | Write_rows     |        10 |         420 | table_id: 124 flags: STMT_END_F       |
| mysql_bin.000002 | 420 | Xid            |        10 |         451 | COMMIT /* xid=992 */                  |
| mysql_bin.000002 | 451 | Anonymous_Gtid |        10 |         516 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'  |
| mysql_bin.000002 | 516 | Query          |        10 |         591 | BEGIN                                 |
| mysql_bin.000002 | 591 | Table_map      |        10 |         649 | table_id: 124 (maqiang.student)       |
| mysql_bin.000002 | 649 | Update_rows    |        10 |         715 | table_id: 124 flags: STMT_END_F       |
| mysql_bin.000002 | 715 | Xid            |        10 |         746 | COMMIT /* xid=995 */                  |
| mysql_bin.000002 | 746 | Anonymous_Gtid |        10 |         811 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'  |
| mysql_bin.000002 | 811 | Query          |        10 |         912 | drop database maqiang                 |
| mysql_bin.000002 | 912 | Rotate         |        10 |         959 | mysql_bin.000003;pos=4                |
+------------------+-----+----------------+-----------+-------------+---------------------------------------+
15 rows in set (0.00 sec)



使用mysqlbinlog恢复差异备份(此时恢复到删除之前的811,在mysql_bin.000002文件里)
[root@localhost ~]# mysqlbinlog --stop-position=811 /opt/data/mysql_bin.000002 |mysql -uroot -pmaqiang123
mysql: [Warning] Using a password on the command line interface can be insecure.


恢复之后就能查看到maqiang数据库，也能查看到增改的内容
[root@localhost ~]# mysql -e 'show databases;'
+--------------------+
| Database           |
+--------------------+
| information_schema |
| linux20200727      |
| maqiang            |
| mysql              |
| performance_schema |
| runtime            |
| sys                |
+--------------------+
[root@localhost ~]# mysql -e 'select * from maqiang.student;'
+----+---------+-------+
| id | name    | score |
+----+---------+-------+
|  1 | tom     |    20 |
|  2 | jerry   |    98 |
|  3 | zs      |    60 |
|  4 | lisi    |    90 |
|  5 | wnagwu  |    87 |
|  6 | qianliu |    67 |
+----+---------+-------+
百慕卿君
关注
  • 1
    点赞
  • 踩
  • 1
    收藏
    觉得还不错? 一键收藏
  • 打赏
    打赏
  • 0
    评论
windows mysql数据库备份_【windows】环境下mysql的数据备份以及恢复
weixin_33835155的博客
01-18 633
【windows】环境下mysql的数据备份以及恢复无论是刚刚入行的‘猿友’还是入行很久的‘老猿’，我相信都会遇到过因为各种原因(很多情况下是自己误删了数据库)的操作.drop databases xxxxx而误删了线上项目的数据库是一件很恐怖的事情，那么如果大家遇到这种情况怎么办呢？首先不要着急(我感觉说了也白说-，-)，先看一看自己的mysql是否开启了binlog日志功能，如果没有？？？ga...
Windows Mysql Online Backup:Windows Mysql 在线备份-开源
07-11
Windows Mysql Online Backup 是一个简单但非常有用的应用程序，用于备份和恢复 mysql 数据库。 主要特点： 1. 定时备份（到远程服务器或到文件）。 2. 在每个 CREATE DATABASE 语句之前添加一个 DROP DATABASE 语句 3. 在每个 CREATE TABLE 语句之前添加一个 DROP TABLE 语句 4. 在每个 CREATE TRIGGER 语句之前添加一个 DROP TRIGGER 语句 5. 用 LOCK TABLES 和 UNLOCK TABLES 语句包围每个表转储 6 . 转储所有数据库中的所有表 7. 允许创建作为关键字的列名 8. 向转储文件添加注释 9. 产生更紧凑的输出 10. 使用包含列名的完整 INSERT 语句 11. 使用 charset_name 作为默认字符集12. 编写 INSERT DELAYED 语句而不是 INSERT 语句 13. 对于每个表，在 INSERT 语句周围加上禁用和启用键的语句 14. 在转储之前锁定所有表等等...
【windows】环境下mysql的数据备份以及恢复
weixin_33922670的博客
09-01 116
【windows】环境下mysql的数据备份以及恢复 　　无论是刚刚入行的‘猿友’还是入行很久的‘老猿’，我相信都会遇到过因为各种原因（很多情况下是自己误删了数据库）的操作.drop databases xxxxx 　　而误删了线上项目的数据库是一件很恐怖的事情，那么如果大家遇到这种情况怎么办呢？首先不要着急（我感觉说了也白说-，-），先看一看自己的mysql是否开启了bin...
mysql 启动指定pid 文件位置
最新发布
ljh574649119的专栏
05-07 930
MySQL服务启动时，可以指定用于存储进程ID文件的位置。这通常通过在启动服务时使用--pid-file选项来完成。如果你想要改变默认的PID文件位置，可以在my.cnf（在Unix系统上通常位于/etc/my.cnf或/etc/mysql/my.cnf）或my.ini（在Windows系统上）中设置。请确保指定的路径是可写的，并且MySQL进程有权限在该位置创建文件。如果指定的位置已经有一个PID文件存在，MySQL将不会覆盖它，除非它属于同一个MySQL服务实例。
windows下mysql 备份和恢复
qq_35853455的博客
05-06 1288
Windows下备份和恢复 1、备份 ①、用管理员身份启动cmd命令行 ②、进入mysql安装目录下bin文件夹内 cd C:\Program Files\MySQL\MySQL Server 5.6\bin ③、执行命令，回车，输入密码，备份文件在mysql bin文件下(xxx是数据库名,aaa是备份后的文件名，可以自己设置) mysqldump -u root -p xxx> aaa....
windows系统mysql备份与恢复
java程序员——佳崴
04-21 282
1.备份 命令解释: -h 主机名称 -u 用户名 -p 备份的数据库名称> 备份到目标文件 在cmd命令中,mysqldump -h localhost -u root -p factmobile>d:/f.sql 2.恢复 前提: 首先在数据库中,创建相应的数据库名称 create ...
mysql进阶学习
10-15
MySQL 进阶学习 MySQL 进阶学习旨在帮助开发者更好地掌握 SQL 语言，提高数据库管理和开发能力。本资源摘要将涵盖 MySQL 进阶学习的主要知识点，包括数据库设计、SQL 优化、查询分析、索引优化、视图和存储过程等。...
MySQL进阶
12-14
DQL:查询语句 1. 排序查询 语法：order by 子句 order by 排序字段1 排序方式1 ， 排序字段2 排序方式2… 排序方式： ASC：升序，默认的。 DESC：降序。 注意： 如果有多个排序条件，则当前边的条件值一样时，才会...
mysql进阶知识整合
09-16
MySQL进阶知识整合涵盖了多个核心领域，包括事务管理、索引、SQL优化、视图、存储过程、锁、逻辑存储引擎和事务原理等。下面将逐一详细阐述这些知识点。 1. **事务**：事务是数据库操作的基本单位，确保一组操作的...
数据库MySQL进阶实战
03-31
《数据库MySQL进阶实战》 MySQL是一个广泛应用的关系型数据库管理系统，源自瑞典MySQL AB公司，现归Oracle公司所有。作为RDBMS（关系数据库管理系统）的杰出代表，MySQL因其开源、小巧、高效的特点，在Web应用领域...
mysql进阶（二）.pdf
09-01
MySQL是一种广泛使用的开源关系型数据库管理系统，其在企业级应用中占据重要地位。本篇主要探讨多表查询、交集、并集、差集、...总之，了解和熟练掌握这些MySQL进阶知识，对于提升数据库管理和应用开发的效率至关重要。
MySQL a库备份恢复为B库_MySQL数据库备份的基础知识_MySQL
weixin_39959569的博客
02-07 268
一、数据备份捷径因为这个方法没有得到官方正式文档的验证，我们暂称为试验吧。目的：备份hostA主机中一个MySQL数据库备份TestA，并恢复到到hostB机中试验环境：操作系统：WinNT4.0,Mysql3.22.34,phpMyAdmin 2.1.0在hostA中安装MySQL数据库备份并建立TestA数据库hostB机安装MySQL数据库备份，没有TestA数据库方法步骤：启动phpMyA...
mysql 5.5 备份,win7 系统下的 mysql 5.5 数据库如何在指令行里备份 和还原数据库
weixin_31655833的博客
03-17 79
关键字: mysql 还原或者导入数据库命令：mysql -u root --password=root备份数据库命令:mysqldump -u root --password=root --database helloworld >helloworld_backup.sql其他用法:1.导出整个数据库mysqldump -u 用户名 -p 数据库名 > 导出的文件名mysqldum...
win7下mysql主从配置文件_Win7下面配置mysql主从备份
weixin_30344519的博客
01-25 94
环境：windows 7 ，mysql-5.5.18-win32.msi主服务器地址：192.168.1.192从数据库地址：192.168.1.193流程：1：在mysql下找到my.ini文件，在其[mysqld] 下添加主服务器端配置：server-id=1 #服务器 idlog-bin=C:\Program Files\MySQL\MySQL Server 5.5\mysql-bin...
win7 mysql备份数据库_win7数据库/文件自动备份(转载)
weixin_29554849的博客
01-26 118
自己创建自动备份程序的优点：1、完全自己定制，可以根据自己的需要建立各类自动处理程序；2、运用操作系统原生服务程序，最大化利用系统资源，减少第三方程序资源消耗；3、自己创建程序，无任何第三方辅助，杜绝服务器受病毒入侵风险；4、运用操作系统功能，减少软件采购成本。一、自动备份批处理程序打开记事本程序，输入(可直接复制粘贴)以下代码：备份数据库批处理代码：@echo offecho 备份78oa数据库...
二、Linux文件、目录操作命令的讲解
胖太乙
01-09 1962
一、命令格式与目录处理命令ls 命令格式：命令 [-选项] [参数] 例如：ls -la /root 说明：1.个别命令可能不遵守上述格式 2.当有多个选项时，可以写在一起。 3.简化选项和完整选项等价，例如：-a 等价于 --all 二、目录处理命令 ls 命令名称：ls 命令英文原意：list 命令所在目录：/bin/ls 执行权限：所有用户 功能描述：显示目录文件...
zabbix第三方邮箱警告配置
weixin_72900594的博客
09-05 3472
zabbix第三方邮箱警告配置
Linux笔记 -- 文件校验
L小Ray想有腮
11-18 320
文章目录md5/sha md5/sha 所处的rpm包 [root@hdmaster ~]# dnf provides md5sum sha1sum sha256sum sha512sum Last metadata expiration check: 0:34:34 ago on Wed 18 Nov 2020 08:30:29 PM CST. coreutils-8.30-7.el8_2.1.x86_64 : A set of basic GNU tools commonly used in she
rocky linux 8.5 基本设置
yuyuyuliang00的博客
06-24 3274
Rocky Linux 常用设置
MySQL进阶实践：查询优化与高可用性策略
"MySQL进阶：优化查询性能与高可用性的实践" MySQL数据库在当今的互联网环境中扮演着核心角色，尤其在大数据量的场景下，优化查询性能和保证高可用性显得尤为重要。本篇将深入讨论这两个关键方面。 一、查询优化 ...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

百慕卿君

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付：¥1
获取中
扫码支付

您的余额不足，请更换扫码支付或充值

打赏作者

实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值