暂无图片
暂无图片
暂无图片
暂无图片
暂无图片
首页 / 原创|NO.A.0025|网络服务|——|mysql|mysqldump和mysqlhotcopy|mysql-binlog|

原创|NO.A.0025|网络服务|——|mysql|mysqldump和mysqlhotcopy|mysql-binlog|

Java BBS 2021-04-07
125

MySQL 数据库-操作基础

MySQL 官网：https://www.mysql.com/

一、MySQL 常见版本

  • MySQL    Community Server 社区版本，开源免费，但不提供官方技术支持。

  • MySQL    Enterprise Edition 企业版本，需付费，可以试用 30 天。

  • MySQL    Cluster 集群版，开源免费。可将几个 MySQL Server 封装成一个 Server。

  • MySQL    Cluster CGE 高级集群版，需付费

二、MySQL 安装部署

  •  MySQL：MySQL 客户端程序

  • MySQL-Server：MySQL 服务器端程序

  1. 源代编译安装：

  • 编译工具：configure、cmake、make

  • 数据库常用的配置选项

-DCMAKE_INSTALL_PREFIX=/PREFIX                  // ----指定安装路径（默认的就是/usr/local/mysql）
-DMYSQL_DATADIR=/data/mysql                     // ----mysql 的数据文件路径
-DSYSCONFDIR=/etc                               // ----配置文件路径
-DWITH_INNOBASE_STORAGE_ENGINE=1                // ----使用 INNOBASE 存储引擎
-DWITH_READLINE=1                               // ----支持批量导入 mysql 数据
-DWITH_SSL=system                               // ----mysql 支持 ssl
-DWITH_ZLIB=system                              // ----支持压缩存储
-DMYSQL_TCP_PORT=3306                           // ----默认端口 3306
-DENABLED_LOCAL_INFILE=1                        // ----启用加载本地数据
-DMYSQL_USER=mysql                              // ----指定 mysql 运行用户
-DMYSQL_UNIX_ADDR=/tmp/mysql.sock               // ----默认套接字文件路径
-DEXTRA_CHARSETS=all                            // ----是否支持额外的字符集
-DDEFAULT_CHARSET=utf8                          // ----默认编码机制
-DWITH_DEBUG=0                                  // ----DEBUG 功能设置

常见资料：

// 服务：mysqld                        
// 端口：3306
// 主配置文件：/etc/my.cnf        
// 初始化脚本：mysql_install_db
// 启动命令：mysqld_safe        
// 数据目录 ：/var/lib/mysql
// 套接字文件：/var/lib/mysql/mysql.sock

注：当意外关闭数据库时，再开启时假如开启不了，找到这个，删除再启动

# 进程文件：/var/run/mysqld/mysqld.pid
// MySQL 登录及退出命令：
#设置密码：mysqladmin
# -uroot password ‘123456’
#登录：mysql -u 用户名 -p 密码 -P 端口 -S 套接字文件
// -p 用户密码
// -h 登陆位置（主机名或 ip 地址）
// -P 端口号（3306 改了就不是了）
// -S 套接字文件（/var/lib/mysql/mysql.sock）
#退出命令：exit 或 ctrl+d

三、 MySQL 管理命令

前提：部署mysql

#服务端：20.20.20.21
#客户端：20.20.20.22
[root@server21 ~]# yum install -y mysql-server mysql
[root@server21 ~]# chkconfig mysqld on
[root@server21 ~]# service mysqld start
[root@server21 ~]# netstat -antp
tcp        0      0 0.0.0.0:3306                0.0.0.0:*                   LISTEN      2048/mysqld  
#查看主配置文件位置
[root@localhost ~]# cat /etc/my.cnf 
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0


[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
//该目录下放着大量的数据库；我们自己保存的时候是.sql文件，默认保存不是.sql文件
[root@server21 ~]# ls /var/lib/mysql/mysql  //启动文件
//[root@localhost ~]# cat /var/run/mysqld/mysqld.pid 
2048
[root@server21 ~]# ps aux |grep 2048                       #2480是mysql的启动进程
mysql      2048  0.0  1.4 377036 27732 pts/2    Sl   09:34   0:00 /usr/libexec/mysqld --basedir=/usr --datadir=/var/lib/mysql --user=mysql --log-error=/var/log/mysqld.log --pid-file=/var/run/mysqld/mysqld.pid --socket=/var/lib/mysql/mysql.sock
root       2072  0.0  0.0 103312   876 pts/2    S+   09:39   0:00 grep 2048
#为mysql设置root账号密码
[root@server21 ~]# mysqladmin -uroot password 123456
#登录
[root@server21 ~]# mysql -u root -p123456 -P 3306 -S /var/lib/mysql/mysql.sock 
mysql>
[root@server21 ~]# mysql -u root -p123456                  #默认写到这个位置即可，端口和sock文件位置是默认的地址
mysql>

1. 创建登录用户

// %:指任意的远程终端'localhost':只允许本地，也可以写单独的IP地址'20.20.20.22'或网段:'20.20.20.0/24'
// identified：申明该账号所使用的密码
mysql> create user zhangsan@'%' identified by '123456';            #允许所有主机登录
Query OK, 0 rows affected (0.00 sec)

2. 测试用户登录

#通过客户端连接数据库
[root@server22 ~]# yum install -y mysql
[root@server22 ~]# mysql -uzhangsan -p123456 -h 20.20.20.21
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| test               |
+--------------------+
2 rows in set (0.00 sec)

3. 用户为自己更改密码

mysql> set password=password('654321');
Query OK, 0 rows affected (0.00 sec)
[root@server22 ~]# mysql -uzhangsan -p123456 -h 20.20.20.21
ERROR 1045 (28000): Access denied for user 'zhangsan'@'20.20.20.22' (using password: YES)
[root@server22 ~]# mysql -uzhangsan -p654321 -h 20.20.20.21
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| test               |
+--------------------+
2 rows in set (0.00 sec)

4. root 用户为其他用户找回密码

[root@server21 ~]# mysql -u root -p123456
mysql> set password for zhangsan@'%'=password('123123');
Query OK, 0 rows affected (0.00 sec)
[root@server22 ~]# mysql -uzhangsan -p123123 -h 20.20.20.21
mysql>

5. root 找回自己的密码并修改

#关闭数据库，修改主配置文件（/etc/my.cnf）添加：skip-grant-tables
[root@server21 ~]# service mysqld stop
[root@server21 ~]# vim /etc/my.cnf
skip-grant-tables                                         #跳过授权表，不进行密码认证
// 启动数据库，空密码登录并修改密码
[root@server21 ~]# service mysqld start
[root@server21 ~]# mysql -uroot                           #跳过密码直接登录
mysql> update mysql.user set password=password('123123') where user='root';  #更新mysql库的user表，新密码为多少，user表示用户
Query OK, 3 rows affected (0.00 sec)
Rows matched: 3  Changed: 3  Warnings: 0
// 删除 skip-grant-tables,重启数据库验证新密码
[root@server21 ~]# vim /etc/my.cnf  
#skip-grant-tables
[root@server21 ~]# service mysqld restart
[root@server21 ~]# mysql -uroot                           #无密码登录失败
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)
[root@server21 ~]# mysql -uroot -p123123                  #密码登录OK
mysql>

6. 创建查询数据库

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| test               |
+--------------------+
3 rows in set (0.00 sec)
#创建数据库
mysql> create database atyanqi;
Query OK, 1 row affected (0.00 sec)


mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| atyanqi            |
| mysql              |
| test               |
+--------------------+
4 rows in set (0.00 sec)

7. 创建数据表

mysql> use atyanqi;
#选择要使用的数据库
mysql> create table a1 (id int,name char(20),age int);
Query OK, 0 rows affected (0.02 sec)
#创建 a1 表，并添加 id 和 name 字段以及类型
mysql> describe a1;
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| id    | int(11)  | YES  |     | NULL    |       |
| name  | char(20) | YES  |     | NULL    |       |
| age   | int(11)  | YES  |     | NULL    |       |
+-------+----------+------+-----+---------+-------+
3 rows in set (0.00 sec)
#查看表结构（字段）

复杂一点的

mysql> use atyanqi;
mysql> create table a2 (
    -> id int unsigned not null auto_increment,            #字段要求为正数、且自增长、主键
    -> name char(20) not null default '',                  #字符型长度 30 字节，默认值为空格
    -> age int not null default 0,                         #字段默认值为 0
    -> primary key (id));                                  #设置 id 为主键
Query OK, 0 rows affected (0.57 sec)
mysql> describe a1;
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| id    | int(11)  | YES  |     | NULL    |       |
| name  | char(20) | YES  |     | NULL    |       |
| age   | int(11)  | YES  |     | NULL    |       |
+-------+----------+------+-----+---------+-------+
3 rows in set (0.01 sec)


mysql> describe a2;
+-------+------------------+------+-----+---------+----------------+
| Field | Type             | Null | Key | Default | Extra          |
+-------+------------------+------+-----+---------+----------------+
| id    | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| name  | char(20)         | NO   |     |         |                |
| age   | int(11)          | NO   |     | 0       |                |
+-------+------------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

8. 插入数据

mysql> use atyanqi;                                      #确认在atyanqi这个库里面
Database changed
mysql> show tables;
+-------------------+
| Tables_in_atyanqi |
+-------------------+
| a1                |
| a2                |
+-------------------+
2 rows in set (0.00 sec)
mysql> select * from a1;
+------+----------+------+
| id   | name     | age  |
+------+----------+------+
|    1 | zhangsan |   18 |
+------+----------+------+
1 row in set (0.00 sec)
mysql> insert into a1 values (2,'lisi',28),(3,'laow',20);          #指明插入字段和数据
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0


mysql> select * from a1;
+------+----------+------+
| id   | name     | age  |
+------+----------+------+
|    1 | zhangsan |   18 |
|    2 | lisi     |   28 |
|    3 | laow     |   20 |
+------+----------+------+
3 rows in set (0.00 sec)
Mysql>insert into a2 values (2,‘lisi’,20)；                #按顺序插入指定字段
Mysql>insert into a2 values (3,‘wangwu’)；                 #未声明年龄
Mysql>insert into a2 values (4,‘zhao’,19)，(5,‘sun’,25)；             #插入多条数据

9. 将表 a2 的数据复制到表 a1

mysql> select * from a1;
+------+----------+------+
| id   | name     | age  |
+------+----------+------+
|    1 | zhangsan |   18 |
|    2 | lisi     |   28 |
|    3 | laow     |   20 |
+------+----------+------+
3 rows in set (0.00 sec)
mysql> insert into a2 (id,name,age) select * from a1;
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0
#查询 a1 值，并写入到 a2
mysql> select * from a2;
+----+----------+-----+
| id | name     | age |
+----+----------+-----+
|  1 | zhangsan |  18 |
|  2 | lisi     |  28 |
|  3 | laow     |  20 |
+----+----------+-----+
3 rows in set (0.00 sec)

10. 删除数据库

mysql> drop tables a1;                                   #删除a1数据库
Query OK, 0 rows affected (0.01 sec)
mysql> show tables;
+-------------------+
| Tables_in_atyanqi |
+-------------------+
| a2                |
+-------------------+
1 row in set (0.00 sec)
mysql> create database abc;
Query OK, 1 row affected (0.00 sec)


mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| abc                |
| atyanqi            |
| mysql              |
| test               |
+--------------------+
5 rows in set (0.00 sec)


mysql> drop database abc;
Query OK, 0 rows affected (0.05 sec)


mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| atyanqi            |
| mysql              |
| test               |
+--------------------+
4 rows in set (0.00 sec)

 11. 删除数据表

Mysql>drop table a1；
Mysql>show table;

12. 删除表里的数据记录

mysql> delete from a2 where id=3;                       #删除 id=5 的记录
Query OK, 1 row affected (0.00 sec)


mysql> select * from a2;
+----+----------+-----+
| id | name     | age |
+----+----------+-----+
|  1 | zhangsan |  18 |
|  2 | lisi     |  28 |
+----+----------+-----+
2 rows in set (0.00 sec)        
mysql> delete from a2 where age between 25 and 30;       #删除年龄在 23-25 之间的
Query OK, 1 row affected (0.00 sec)


mysql> select * from a2;
+----+----------+-----+
| id | name     | age |
+----+----------+-----+
|  1 | zhangsan |  18 |
+----+----------+-----+
1 row in set (0.00 sec)

注：库和表的删除用 drop,记录删除用 delete

13. 修改表中的数据

Mysql>update a2 set age=21 where id=3;

14. 修改数据表的名称

mysql> alter table a2 rename a1;
Query OK, 0 rows affected (0.00 sec)


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


mysql> describe a1;
+-------+------------------+------+-----+---------+----------------+
| Field | Type             | Null | Key | Default | Extra          |
+-------+------------------+------+-----+---------+----------------+
| id    | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| name  | char(20)         | NO   |     |         |                |
| age   | int(11)          | NO   |     | 0       |                |
+-------+------------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

15. 修改数据表的字段类型

mysql> describe a1;
+-------+------------------+------+-----+---------+----------------+
| Field | Type             | Null | Key | Default | Extra          |
+-------+------------------+------+-----+---------+----------------+
| id    | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| name  | char(20)         | NO   |     |         |                |
| age   | int(11)          | NO   |     | 0       |                |
+-------+------------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)


mysql> alter table a1 modify name char(30);
Query OK, 1 row affected (0.02 sec)
Records: 1  Duplicates: 0  Warnings: 0


mysql> describe a1;
+-------+------------------+------+-----+---------+----------------+
| Field | Type             | Null | Key | Default | Extra          |
+-------+------------------+------+-----+---------+----------------+
| id    | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| name  | char(30)         | YES  |     | NULL    |                |
| age   | int(11)          | NO   |     | 0       |                |
+-------+------------------+------+-----+---------+----------------+
3 rows in set (0.01 sec)

16. 修改数据表的字段类型详情

mysql> describe a1;
+-------+------------------+------+-----+---------+----------------+
| Field | Type             | Null | Key | Default | Extra          |
+-------+------------------+------+-----+---------+----------------+
| id    | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| name  | char(30)         | YES  |     | NULL    |                |
| age   | int(11)          | NO   |     | 0       |                |
+-------+------------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)
mysql> alter table a1 change name username char(50) not null default '';
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'defaul''' at line 1
mysql> alter table a1 change name username char(50) not null default '';
Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0
mysql> describe a1;
+----------+------------------+------+-----+---------+----------------+
| Field    | Type             | Null | Key | Default | Extra          |
+----------+------------------+------+-----+---------+----------------+
| id       | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| username | char(50)         | NO   |     |         |                |
| age      | int(11)          | NO   |     | 0       |                |
+----------+------------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

17. 添加字段

mysql> describe a1;
+----------+------------------+------+-----+---------+----------------+
| Field    | Type             | Null | Key | Default | Extra          |
+----------+------------------+------+-----+---------+----------------+
| id       | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| username | char(50)         | NO   |     |         |                |
| age      | int(11)          | NO   |     | 0       |                |
+----------+------------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)


mysql> alter table a1 add time datetime;
Query OK, 1 row affected (0.07 sec)
Records: 1  Duplicates: 0  Warnings: 0


mysql> describe a1;
+----------+------------------+------+-----+---------+----------------+
| Field    | Type             | Null | Key | Default | Extra          |
+----------+------------------+------+-----+---------+----------------+
| id       | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| username | char(50)         | NO   |     |         |                |
| age      | int(11)          | NO   |     | 0       |                |
| time     | datetime         | YES  |     | NULL    |                |
+----------+------------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)
#添加位置默认在末尾
mysql> alter table a1 add birthday year first;              #添加字段到第一列
Query OK, 1 row affected (0.04 sec)
Records: 1  Duplicates: 0  Warnings: 0


mysql> describe a1;
+----------+------------------+------+-----+---------+----------------+
| Field    | Type             | Null | Key | Default | Extra          |
+----------+------------------+------+-----+---------+----------------+
| birthday | year(4)          | YES  |     | NULL    |                |
| id       | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| username | char(50)         | NO   |     |         |                |
| age      | int(11)          | NO   |     | 0       |                |
| sex      | char(1)          | YES  |     | NULL    |                |
| time     | datetime         | YES  |     | NULL    |                |
+----------+------------------+------+-----+---------+----------------+
6 rows in set (0.00 sec)    
mysql> describe a1;          
+----------+------------------+------+-----+---------+----------------+
| Field    | Type             | Null | Key | Default | Extra          |
+----------+------------------+------+-----+---------+----------------+
| id       | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| username | char(50)         | NO   |     |         |                |
| age      | int(11)          | NO   |     | 0       |                |
| time     | datetime         | YES  |     | NULL    |                |
+----------+------------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)


mysql> alter table a1 add sex char(1) after age;            #添加到指定字段后
Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0


mysql> describe a1;            
+----------+------------------+------+-----+---------+----------------+
| Field    | Type             | Null | Key | Default | Extra          |
+----------+------------------+------+-----+---------+----------------+
| id       | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| username | char(50)         | NO   |     |         |                |
| age      | int(11)          | NO   |     | 0       |                |
| sex      | char(1)          | YES  |     | NULL    |                |
| time     | datetime         | YES  |     | NULL    |                |
+----------+------------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)

18. 删除字段

mysql> describe a1;
+----------+------------------+------+-----+---------+----------------+
| Field    | Type             | Null | Key | Default | Extra          |
+----------+------------------+------+-----+---------+----------------+
| birthday | year(4)          | YES  |     | NULL    |                |
| id       | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| username | char(50)         | NO   |     |         |                |
| age      | int(11)          | NO   |     | 0       |                |
| sex      | char(1)          | YES  |     | NULL    |                |
| time     | datetime         | YES  |     | NULL    |                |
+----------+------------------+------+-----+---------+----------------+
6 rows in set (0.01 sec)


mysql> alter table a1 drop birthday;
Query OK, 1 row affected (0.07 sec)
Records: 1  Duplicates: 0  Warnings: 0


mysql> describe a1;
+----------+------------------+------+-----+---------+----------------+
| Field    | Type             | Null | Key | Default | Extra          |
+----------+------------------+------+-----+---------+----------------+
| id       | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| username | char(50)         | NO   |     |         |                |
| age      | int(11)          | NO   |     | 0       |                |
| sex      | char(1)          | YES  |     | NULL    |                |
| time     | datetime         | YES  |     | NULL    |                |
+----------+------------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)

19. Mysql 用户授权

# 授予用户全部权限
[root@server22 ~]# mysql -uzhangsan -p123123 -h 20.20.20.21          #张三是没有对atyanqi授权你库的权限
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| test               |
+--------------------+
2 rows in set (0.00 sec)
Mysql>select user from mysql.user;                         #给已存在用户授权
#使用root登录权限修改用户授权
mysql> grant all on atyanqi.* to zhangsan@'%';
Query OK, 0 rows affected (0.00 sec)
#在20.20.20.22客户端张三用户查看授权结果
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| atyanqi            |
| test               |
+--------------------+
3 rows in set (0.00 sec)
mysql> grant all on atyanqi.* to lisi@'%' identified by '123456';      #创建用户并授权
Query OK, 0 rows affected (0.00 sec)  
[root@server22 ~]# mysql -ulisi -p123456 -h 20.20.20.21
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| atyanqi            |
| test               |
+--------------------+
3 rows in set (0.00 sec)

取消 abc 用户的删除库、表、表中数据的权限

mysql> show grants for lisi@'%';                           #查看指定用户的授权
+-----------------------------------------------------------------------------------------------------+
| Grants for lisi@%                                                                                   |
+-----------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'lisi'@'%' IDENTIFIED BY PASSWORD '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9' |
| GRANT ALL PRIVILEGES ON `atyanqi`.* TO 'lisi'@'%'                                                   |
+-----------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)            
mysql> revoke drop,delete on atyanqi.* from lisi@'%';      #取消删除权限（登录 atyanqi 测试）
Query OK, 0 rows affected (0.00 sec)


mysql> show grants for lisi@'%';
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Grants for lisi@%                                                                                                                                                                                                 |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'lisi'@'%' IDENTIFIED BY PASSWORD '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9'                                                                                                               |
| GRANT SELECT, INSERT, UPDATE, CREATE, REFERENCES, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, EVENT, TRIGGER ON `atyanqi`.* TO 'lisi'@'%' |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)


Mysql>show grants for atyanqi@‘%’；

四、 备份和还原

mysqldump 备份：

#备份：
// mysqldump -u 用户名 -p 数据库名 > /备份路径/备份文件名（备份整个数据库）
// mysqldump -u 用户名 -p 数据库名 表名 > /备份路径/备份文件名（备份数据表）
#备份多个库：--databases 库 1，库 2
#备份所有库：--all-databases
#备份多个表：库名 表 1 表 2
//1、备份数据库
[root@server21 ~]# mkdir /mysqldate
[root@server21 ~]# mysqldump -uroot -p123123 atyanqi > /mysqldate/atyanqi.sql
[root@server21 ~]# ls /mysqldate/
atyanqi.sql
#还原：mysql 数据库 < 备份文件
// 注意：还原时，若导入的是某表，请指定导入到哪一个库中
#mysqlhotcopy 备份：
#备份：mysqlhotcopy --flushlog -u=’用户’ -p=’密码’ --regexp=正则 备份目录
#还原：cp -a 备份目录 数据目录（/var/lib/mysql）
//2、客户端还原
[root@server22 ~]# yum install -y mysql-server
[root@server21 ~]# scp -r  /mysqldate/atyanqi.sql root@20.20.20.22:/root/ 
[root@server22 ~]# servoce mysqld start 
[root@server22 ~]# mysql -uroot 
 mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| test               |
+--------------------+
3 rows in set (0.00 sec)
//导入备份的数据库
mysql> create database atyanqi;
Query OK, 1 row affected (0.00 sec)
[root@server22 ~]# mysql atyanqi < atyanqi.sql 
[root@server22 ~]# mysql
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| atyanqi            |
| mysql              |
| test               |
+--------------------+
4 rows in set (0.00 sec)

mysqldump 和 mysqlhotcopy 示例：

#备份数据库atyanqi
[root@server21 ~]# ls /var/lib/mysql/
atyanqi 
[root@server21 ~]# cp -a /var/lib/mysql/atyanqi/ /
[root@server21 ~]# ls /atyanqi/
#删除原有数据库文件atyanqi
[root@server21 ~]# rm -rf /var/lib/mysql/atyanqi/
[root@server21 ~]# mysql -uroot -p123123
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| test               |
+--------------------+
3 rows in set (0.00 sec)
[root@server21 ~]# mv /atyanqi/ /var/lib/mysql/
[root@server21 ~]# mysql -uroot -p123123
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| atyanqi            |
| mysql              |
| test               |
+--------------------+
4 rows in set (0.00 sec)
// Mysql 备份和还原
// 把数据库 aa 备份到/root 目录下
# mysqldump –uroot –p aa > ~/aa.sql
// 模拟数据库 aa 丢失（删除数据库 aa）
# Mysql>drop database aa;
// 通过 aa.sql 文件还原（指定导入到哪个库中）
# mysql –uroot –p test < aa.sql
// 备份多个数据库（--databases）
# mysqldump –uroot –p --databases aa test > abc.sql
// 还原（先模拟丢失）
# mysql –uroot –p < abc.sql
// 备份有规则的数据库
Mysql>create database a1;                                 #连续创建三个 a 开头的数据库
# mysqlhotcopy --flushlog –u=‘root’ –p=‘456’ --regexp=^a
// 还原（先模拟丢失）
Mysql>drop database a1;                                   #顺序删除 a 开头的数据库
# cp –a /mnt/* /var/lib/mysql/                            #复制产生的文件到数据库目录下
// 登录数据库查看即可

mysql-binlog 日志备份：

// 二进制日志（log-bin 日志）：所有对数据库状态更改的操作（create、drop、update 等）
// 修改 my.cnf 配置文件开启 binlog 日志记录功能


// 按时间还原：
--start-datetime
--stop-datetime
// 格式：mysqlbinlog --start-datetime ‘YY-MM-DD HH:MM:SS’ --stop-datetime ‘YY-MM-DD
// HH:MM:SS’ 二进制日志 | mysql -uroot -p
[root@server21 ~]# mysqlbinlog /var/lib/mysql/mysql-bin.000001        #还没有日志记录
/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
mysqlbinlog: File 'mysql-bin.000001' not found (Errcode: 2)
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
[root@server21 ~]# mysql -uroot -p123123
mysql> create database abc;
Query OK, 1 row affected (0.00 sec)
mysql> use abc;
Database changed
mysql> create table xxx (id int,name char(10));
Query OK, 0 rows affected (0.05 sec)
mysql> insert into xxx values (1,'aaaa'),(2,'bbbb');
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0


mysql> select * from xxx
    -> Ctrl-C -- exit!
Aborted
[root@server21 ~]# mysql -uroot -p123123
mysql> use abc;
Database changed
mysql> select * from xxx;
+------+------+
| id   | name |
+------+------+
|    1 | aaaa |
|    2 | bbbb |
+------+------+
2 rows in set (0.00 sec)
[root@server21 ~]# mysqlbinlog /var/lib/mysql/mysql-bin.000001
create database abc
/*!*/;
# at 187
#210204 16:19:05 server id 1  end_log_pos 288   Query  thread_id=2  exec_time=0  error_code=0
use `abc`/*!*/;
SET TIMESTAMP=1612426745/*!*/;
create table xxx (id int,name char(10))
/*!*/;
# at 288
#210204 16:20:11 server id 1  end_log_pos 394   Query  thread_id=2  exec_time=0  error_code=0
SET TIMESTAMP=1612426811/*!*/;
insert into xxx values (1,'aaaa'),(2,'bbbb')
/*!*/;
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;


// 按文件大小还原：
--start-position
--stop-position

mysql-binlog 日志备份示例：

开启二进制日志

[root@server21 ~]# service mysqld stop
[root@server21 ~]# vim /etc/my.cnf
log-bin=mysql-bin                                         #启动二进制日志
[root@server21 ~]# service mysqld start

查看二进制日志文件

[root@server21 ~]# ls /var/lib/mysql/
mysql-bin.000001                                          #会生成一个这样的文件，该日志只记录数据的增删改，不记录查询的数据

按时间还原：

如果数据库中的 bb 库被删，需要还原

#模拟数据丢失
[root@server21 ~]# mv /var/lib/mysql/abc/ /tmp/
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| atyanqi            |
| mysql              |
| test               |
+--------------------+
4 rows in set (0.00 sec)
#按照二进制日志的方式恢复；找到创建数据库的时间点，再确定数据库删除的最后时间点
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| abc                |
| atyanqi            |
| mysql              |
| test               |
+--------------------+
5 rows in set (0.00 sec)


mysql> drop database abc;
Query OK, 0 rows affected (0.01 sec)


mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| atyanqi            |
| mysql              |
| test               |
+--------------------+
4 rows in set (0.00 sec)
#查看二进制日志：  #找到两个at之间的时间节点。
[root@server21 ~]# mysqlbinlog /var/lib/mysql/mysql-bin.000001
#210204 16:19:05 server id 1  end_log_pos 288   Query  thread_id=2  exec_time=0  error_code=0
use `abc`/*!*/;
SET TIMESTAMP=1612426745/*!*/;
create table xxx (id int,name char(10))
/*!*/;
# at 288
#210204 16:20:11 server id 1  end_log_pos 394   Query  thread_id=2  exec_time=0  error_code=0
SET TIMESTAMP=1612426811/*!*/;
insert into xxx values (1,'aaaa'),(2,'bbbb')              #数据库写入的时间点
/*!*/;
# at 394
#210204 16:18:27 server id 1  end_log_pos 475   Query  thread_id=2  exec_time=951  error_code=0
SET TIMESTAMP=1612426707/*!*/;
create database abc
/*!*/;
# at 475
#210204 16:18:27 server id 1  end_log_pos 556   Query  thread_id=2  exec_time=1132  error_code=0
SET TIMESTAMP=1612426707/*!*/;
create database abc
/*!*/;
# at 556
#210204 16:42:36 server id 1  end_log_pos 635   Query  thread_id=12  exec_time=0  error_code=0
SET TIMESTAMP=1612428156/*!*/;
drop database abc                                         #数据库删除的时间点
#按时间点查看数据库
[root@server21 ~]# mysqlbinlog --start-datetime "2021-02-04 16:15:46" --stop-datetime "2021-02-04 16:43:36" /var/lib/mysql/mysql-bin.000001 | mysql -uroot -p123123
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| abc                |
| atyanqi            |
| mysql              |
| test               |
+--------------------+
5 rows in set (0.00 sec)


mysql> use abc;
Database changed
mysql> show tables;
+---------------+
| Tables_in_abc |
+---------------+
| xxx           |
+---------------+
1 row in set (0.00 sec)


mysql> select * from xxx;
+------+------+
| id   | name |
+------+------+
|    1 | aaaa |
|    2 | bbbb |
+------+------+
2 rows in set (0.00 sec)

注：所选时间段一定要完整包含所有动作（可以在原来基础上稍微增加点时间）

按文件大小还原：还原到 bb 库被删除的数据状态

1.查看 bb 库被删除前后的文件大小

========================================END======================================

辅助文本

三、 MySQL 管理命令

1. 创建登录用户

mysql>create user zhangsan@‘%’ identified by ‘123456’;
// %:指任意的远程终端

2. 测试用户登录

# yum -y install mysql
# mysql -uzhangsan -p123456 -h 192.168.88.10

3. 用户为自己更改密码

mysql>set password=password（‘123456’）;

4. root 用户为其他用户找回密码

mysql>set password for atguigu@‘%’=password(‘123123’);

5. root 找回自己的密码并修改

// 关闭数据库，修改主配置文件（/etc/my.cnf）添加：skip-grant-tables
vim /etc/my.cnf
// skip-grant-tables
// 启动数据库，空密码登录并修改密码
// update mysql.user set password=password(‘新密码’) where user=’root’;
// 删除 skip-grant-tables,重启数据库验证新密码

6. 创建查询数据库

mysql>create database web;
mysql>show databases;

7. 创建数据表

Mysql>use web;
#选择要使用的数据库
Mysql>create table a1 (id int ,name char(30));
#创建 a1 表，并添加 id 和 name 字段以及类型
Mysql>describe a1;
#查看表结构（字段）

复杂一点的

Mysql>create table a2 (
->id int unsigned not null auto_increment,                  #字段要求为正数、且自增长、主键
->name char(30) not null default ‘’,                        #字符型长度 30 字节，默认值为空格
->age int not null default 0,                               #字段默认值为 0
->primary key (id));                                        #设置 id 为主键
Mysql> describe a2;

8. 插入数据

Mysql>insert into a2 (id,name,age) values (1,‘zhangsan’,21)；#指明插入字段和数据
Mysql>select * from a2;
Mysql>insert into a2 values (2,‘lisi’,20)；#按顺序插入指定字段
Mysql>insert into a2 values (3,‘wangwu’)；#未声明年龄
Mysql>insert into a2 values (4,‘zhao’,19)，(5,‘sun’,25)；#插入多条数据

9. 将表 a2 的数据复制到表 a1

Mysql>select * from a1;
Mysql>insert into a1 (id,name) select id,name from a2;
#查询 a2 值，并写入到 a1
Mysql>select * from a1;

10. 删除数据库

Mysql>drop database abc；
Mysql>show databases;

 11. 删除数据表

Mysql>drop table a1；
Mysql>show table;

12. 删除表里的数据记录

Mysql>delete from a2 where id=5;                          #删除 id=5 的记录
Mysql>delete from a2 where between 23 and 25;             #删除年龄在 23-25 之间的

注：库和表的删除用 drop,记录删除用 delete

13. 修改表中的数据

Mysql>update a2 set age=21 where id=3;

14. 修改数据表的名称

Mysql>alter table a2 rename a1;

15. 修改数据表的字段类型

Mysql>describe a1;
Mysql>alter table a1 modify name char(50);
Mysql>describe a1;

16. 修改数据表的字段类型详情

Mysql>describe a1;
Mysql>alter table a1 change name username char(50) not null default ‘’;
Mysql>describe a1

17. 添加字段

Mysql>describe a1;
Mysql>alter table a1 add time datetime;
Mysql>describe a1;
#添加位置默认在末尾
Mysql>alter table a1 add birthday year first;             #添加字段到第一列
Mysql>alter table a1 add sex nchar(1) after id;           #添加到指定字段后

18. 删除字段

Mysql>alter table a1 drop birthday;

19. Mysql 用户授权

# 授予用户全部权限
Mysql>select user from mysql.user;                                  #给已存在用户授权
Mysql>grant all on aa.a1 to atguigu@‘%’；
Mysql>grant all on aa.a1 to abc@‘%’ identified by ‘123456’； #创建用户并授权

 取消 abc 用户的删除库、表、表中数据的权限

Mysql>revoke drop,delete on aa.a1 from abc@‘%’；            #取消删除权限（登录 abc 测试）
Mysql>show grants for abc@‘%’；                             #查看指定用户的授权
Mysql>show grants for atguigu@‘%’；

四、 备份和还原

mysqldump 备份：

// 备份：
// mysqldump -u 用户名 -p 数据库名 > /备份路径/备份文件名（备份整个数据库）
// mysqldump -u 用户名 -p 数据库名 表名 > /备份路径/备份文件名（备份数据表）
// 备份多个库：--databases 库 1，库 2
// 备份所有库：--all-databases
// 备份多个表：库名 表 1 表 2
// 还原：mysql 数据库 < 备份文件
// 注意：还原时，若导入的是某表，请指定导入到哪一个库中
// mysqlhotcopy 备份：
// 备份：mysqlhotcopy --flushlog -u=’用户’ -p=’密码’ --regexp=正则 备份目录
// 还原：cp -a 备份目录 数据目录（/var/lib/mysql）

mysqldump 和 mysqlhotcopy 示例：

// Mysql 备份和还原
// 把数据库 aa 备份到/root 目录下
# mysqldump –uroot –p aa > ~/aa.sql
// 模拟数据库 aa 丢失（删除数据库 aa）
# Mysql>drop database aa;
// 通过 aa.sql 文件还原（指定导入到哪个库中）
# mysql –uroot –p test < aa.sql
// 备份多个数据库（--databases）
# mysqldump –uroot –p --databases aa test > abc.sql
// 还原（先模拟丢失）
# mysql –uroot –p < abc.sql
// 备份有规则的数据库
Mysql>create database a1;                                           #连续创建三个 a 开头的数据库
# mysqlhotcopy --flushlog –u=‘root’ –p=‘456’ --regexp=^a
// 还原（先模拟丢失）
Mysql>drop database a1;                                             #顺序删除 a 开头的数据库
# cp –a /mnt/* /var/lib/mysql/                                      #复制产生的文件到数据库目录下
// 登录数据库查看即可

mysql-binlog 日志备份：

// 二进制日志（log-bin 日志）：所有对数据库状态更改的操作（create、drop、update 等）
// 修改 my.cnf 配置文件开启 binlog 日志记录功能
# vim /etc/my.cnf
log-bin=mysql-bin                                         #启动二进制日志
// 按时间还原：
--start-datetime
--stop-datetime
// 格式：mysqlbinlog --start-datetime ‘YY-MM-DD HH:MM:SS’ --stop-datetime ‘YY-MM-DD
// HH:MM:SS’ 二进制日志 | mysql -uroot -p
// 按文件大小还原：
--start-position
--stop-position

mysql-binlog 日志备份示例：

开启二进制日志

查看二进制日志文件

按时间还原：

如果数据库中的 bb 库被删，需要还原

查看二进制日志内容

还原并查看

mysqlbinlog --start-datetime=‘2018-09-11 14:24:00’ --stop-datetime=‘2018-09-11 14:28:00’
mysql-bin.000006 | mysql –uroot –p123123

注：所选时间段一定要完整包含所有动作（可以在原来基础上稍微增加点时间）

按文件大小还原：还原到 bb 库被删除的数据状态

1.查看 bb 库被删除前后的文件大小

还原并查看

END





声明：JavaBBS论坛主要用于IT技术专题的交流学习，为开源技术爱好者提供广泛、权威的技术资料。若您在技术专题有更好的想法或者建议，欢迎交流！！！

推荐阅读

Recommended reading

 







JavaBBS



Git



→






  






https://www.javabbs.cn/git






  JavaBBS大数据






→ 






https://www.javabbs.cn





/dsj





  JavaBBS云存储






→  https://www.javabbs.cn






/ycc







  JavaBBS数据库






→






  https://www.javabbs.cn






/sjk







  JavaBBS云计算






→






  https://www.javabbs.cn






/yjs







  JavaBBSIT.Log






→






https://www.javabbs.cn






/itl






  JavaBBSNginx






→






  https://www.javabbs.cn






/ngx






  JavaBBSzabbix






→






https://www.javabbs.cn






/zbx






  JavaBBSJavaSE






→






https://www.javabbs.cn






/jse







  JavaBBS社区文章






→






https://www.javabbs.cn






/bwz






  JavaBBS社区资料






→






https://www.javabbs.cn






/bzl







mysql
文章转载自Java BBS，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

评论