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

-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

#服务端：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 -antptcp        0      0 0.0.0.0:3306                0.0.0.0:*                   LISTEN      2048/mysqld  #查看主配置文件位置[root@localhost ~]# cat /etc/my.cnf [mysqld]datadir=/var/lib/mysqlsocket=/var/lib/mysql/mysql.sockuser=mysql# Disabling symbolic-links is recommended to prevent assorted security riskssymbolic-links=0[mysqld_safe]log-error=/var/log/mysqld.logpid-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.sockroot       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>

// %:指任意的远程终端'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)

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

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

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

#关闭数据库，修改主配置文件（/etc/my.cnf）添加：skip-grant-tables[root@server21 ~]# service mysqld stop[root@server21 ~]# vim /etc/my.cnfskip-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                  #密码登录OKmysql> 

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)

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)

mysql> use atyanqi;                                      #确认在atyanqi这个库里面Database changedmysql> 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: 0mysql> 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)；             #插入多条数据

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 值，并写入到 a2mysql> select * from a2;+----+----------+-----+| id | name     | age |+----+----------+-----+|  1 | zhangsan |  18 ||  2 | lisi     |  28 ||  3 | laow     |  20 |+----+----------+-----+3 rows in set (0.00 sec)

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)

Mysql>drop table a1；Mysql>show table;

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)  

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

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)

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: 0mysql> 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)

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 1mysql> alter table a1 change name username char(50) not null default '';Query OK, 1 row affected (0.00 sec)Records: 1  Duplicates: 0  Warnings: 0mysql> 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> 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: 0mysql> 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: 0mysql> 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: 0mysql> 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)    

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: 0mysql> 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)

# 授予用户全部权限[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.21mysql> show databases;+--------------------+| Database           |+--------------------+| information_schema || atyanqi            || test               |+--------------------+3 rows in set (0.00 sec)   

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 -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 ~]# mysqlmysql> show databases;+--------------------+| Database           |+--------------------+| information_schema || atyanqi            || mysql              || test               |+--------------------+4 rows in set (0.00 sec)

#备份数据库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 -p123123mysql> 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 -p123123mysql> 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/                            #复制产生的文件到数据库目录下// 登录数据库查看即可

// 二进制日志（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 fileROLLBACK /* added by mysqlbinlog */;/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;[root@server21 ~]# mysql -uroot -p123123mysql> create database abc;Query OK, 1 row affected (0.00 sec)mysql> use abc;Database changedmysql> 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: 0mysql> select * from xxx    -> Ctrl-C -- exit!Aborted[root@server21 ~]# mysql -uroot -p123123mysql> use abc;Database changedmysql> select * from xxx;+------+------+| id   | name |+------+------+|    1 | aaaa ||    2 | bbbb |+------+------+2 rows in set (0.00 sec)[root@server21 ~]# mysqlbinlog /var/lib/mysql/mysql-bin.000001create database abc/*!*/;# at 187#210204 16:19:05 server id 1  end_log_pos 288   Query  thread_id=2  exec_time=0  error_code=0use `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=0SET TIMESTAMP=1612426811/*!*/;insert into xxx values (1,'aaaa'),(2,'bbbb')/*!*/;DELIMITER ;# End of log fileROLLBACK /* added by mysqlbinlog */;/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;// 按文件大小还原：--start-position--stop-position

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

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

#模拟数据丢失[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=0use `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=0SET 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=0SET 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=0SET 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=0SET 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 -p123123mysql> show databases;+--------------------+| Database           |+--------------------+| information_schema || abc                || atyanqi            || mysql              || test               |+--------------------+5 rows in set (0.00 sec)mysql> use abc;Database changedmysql> 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)

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

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

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

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

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

mysql>create database web;mysql>show databases;

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;

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)；#插入多条数据

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

Mysql>drop database abc；Mysql>show databases;

Mysql>drop table a1；Mysql>show table;

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

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

Mysql>alter table a2 rename a1;

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

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

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;           #添加到指定字段后

Mysql>alter table a1 drop birthday;

# 授予用户全部权限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’； #创建用户并授权

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

// 备份：// 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）

// 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/                                      #复制产生的文件到数据库目录下// 登录数据库查看即可

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

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