来，说说如下命令的区别drop table new_students;truncate table new_students;delete from new_students; 区别？
回答
SHELL
4.8.DQL数据查询语言
SHELL
1.导入数据[root@db-51 ~]#mysql -uroot -p123456 < all_db_backup.sql mysql: [Warning] Using a password on the command line interface can be insecure.
SHELL
查看表创建语句
mysql> show create table country;mysql> show create table city;
select语句
查询数据库服务端配置
SHELL
查询mysql加载的变量
SHELL
查看mysql内置函数 函数就是可以复用的一个功能代码
SHELL
常见函数执行
SHELL
Where 子查询
mysql强大之处在于基于数据表，进行复杂的查询，适用于各种复杂业务场景下，对数据的查询提取。
子查询几个关键语句执行顺序如下
DQL系列数据查询语句，作为后端开发重点，运维学习了解即可
运维以架构部署，提供数据库环境，保障数据库稳定性为主。
SHELL
子查询语句顺序
查询city表中所有数据
SHELL
或者
SHELL
select ID,Name,CountryCode,District,Population from world.city;
查询城市信息
查询所有城市名，以及人口数
SHELL
# 查询所有城市、人口数量select name,Population from city;
查询city表里，所有中国的城市信息
SHELL
select * from city where countrycode ='CHN';
查询人口数小于500人城市信息
SHELL
select * from city where Population < 500;
查询中国,人口数超过500w的所有城市信息
SHELL
select * from city where CountryCode='chn' and Population>
查询中国或者美国的城市信息
SHELL
查询人口在200万到500万之间的城市所有信息
SHELL
解法1
查询中国或者美国，人口数大于500万的城市
SHELL
查询城市名以qing开头的城市
SHELL
select * from city where name like 'qing%'
查询淮安市的人口数量
SHELL
select * from city where name like 'huai%'
Group by 聚合函数
常用聚合函数
SHELL
group by 分组功能原理
SHELL
1. 按分组条件进行排序2. 进行分组列的去重3. 聚合函数将其他列的结果聚合
group by聚合函数练习题
统计city表有多少行数据
SHELL
select count(*) from city;
统计中国城市的个数
SHELL
select count(*) from city where CountryCode='CHN';
统计中国总人口数
SHELL
select sum(Population) from city where CountryCode='CHN'
统计每个国家的城市个数
SHELL
SELECT
统计每个国家的总人口数
SHELL
select CountryCode,sum(Population) as people_num from city group by CountryCodeselect CountryCode,sum(Population) as people_num from city group by CountryCode
统计中国每个省的城市个数，和城市名
SHELL
having 聚合判断
当group by 排序分组后，如果还要做条件判断。
Having 练习题
统计每个国家的总人口数，只显示人口超过1亿的国家信息
SHELL
# 统计每个国家的总人口数，只显示人口超过1亿的国家信息select CountryCode,sum(Population) as 人口总数 from city group by CountryCode having sum(Population) > 100000000
Order by 聚合排序
查询所有城市信息，按人口数排序输出，正序输出
SHELL
select * from city order by Population逆序输出select * from city order by Population desc
查询中国所有城市信息，且按照人口数从大到小排序输出
SHELL
select * from city where CountryCode='chn' order by 'Population' desc
每个国家的总人口数，总人口超过5000w的信息,并按总人口数从大到小排序输出
SHELL
# 每个国家的总人口数，总人口超过5000w的信息,并按总人口数从大到小排序输出select CountryCode,sum(Population) as 人口总数 from city GROUP BY CountryCode having sum(Population) > 50000000 order by sum(Population
--all--database，-A 转储所有数据库中的所有表。[root@db-51 ~]#mysqldump -uroot -p123456 -A > /mysql_backup/all_db.sqlmysqldump: [Warning] Using a password on the command line interface can be insecure.
grant replication slave on*.* to repl@'10.0.0.%' identified by 'www.yuchaoit.cn';
主库gtid检查
从库操作
mysql> show slave status\GEmpty set (0.00 sec)change master to master_host='10.0.0.51', master_user='repl', master_password='www.yuchaoit.cn' , MASTER_AUTO_POSITION=1;start slave;
测试主库事务更新
基于gtid的主从复制结果
echo
'export PATH=$PATH:/opt/mysql/bin'
>>
/etc/profile
source /etc/profile
[root@db-51 /opt]#mysql -V
mysql Ver 14.14 Distrib 5.7.28, for linux-glibc2.12 (x86_64) using EditLine wrapper
设置密码，默认空密码[root@db-51 ~]#mysqladmin password -S /tmp/mysql.sock 123456mysqladmin: [Warning] Using a password on the command line interface can be insecure.Warning: Since password will be sent to server in plain text, use ssl connection to ensure password safety.[root@db-51 ~]#mysql -uroot -p123456mysql: [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 3Server version: 5.7.28 MySQL Community Server (GPL)Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.mysql>
mysql -uroot -p123456 -S /mysql_test/mysql_3307/mysql.sock -e "show global variables like 'port';"mysql -uroot -p123456 -S /mysql_test/mysql_3308/mysql.sock -e "show global variables like 'port';"
cat > /etc/systemd/system/mysqld.service <<'EOF'[Unit]Description=mysql server by www.yuchaoit.cnDocumentation=man:mysqld(8)Documentation=https://dev.mysql.com/doc/refman/en/using-systemd.htmlAfter=network.targetAfter=syslog.target[Install]WantedBy=multi-user.target[Service]User=mysqlGroup=mysqlExecStart=/opt/mysql/bin/mysqld --defaults-file=/etc/my.cnfLimitNOFILE=5000EOF
[root@localhost.localdomain /mysql_test]#mysql -uroot -p123456 -e "select User,Host,authentication_string from mysql.user;"mysql: [Warning] Using a password on the command line interface can be insecure.+---------------+-----------+-------------------------------------------+| User | Host | authentication_string |+---------------+-----------+-------------------------------------------+| root | localhost | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 || mysql.session | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE || mysql.sys | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE || ningbo | localhost | *CE257D70249031FEB9EE9D908E2C87A1CFE7155C |+---------------+-----------+-------------------------------------------+4 rows in set (0.00 sec)
[root@localhost.localdomain ~]#mysql -uningbo -pningbo666 -S /tmp/mysql.sock -e "show databases;"mysql: [Warning] Using a password on the command line interface can be insecure.+--------------------+| Database |+--------------------+| information_schema || mysql || performance_schema || sys |+--------------------+
# 只允许yuchao02用户在 10.0.0.x 网段登录，有最大的权限
# 授权语句只能用root去操作
mysql -uroot -p123456 -S /tmp/mysql.sock -e "grant all on *.* to ningbo02@'10.0.0.%' identified by 'ningbo666';"
mysql -uroot -p123456 -S /tmp/mysql.sock -e "grant all on *.* to bob01@'10.0.0.7' identified by 'bob666';"mysql 用户授权语法用户名@'网段白名单'语法yuchao@'localhost' yuchao可以在本地登录（ip:3306），以及socketyuchao@'10.0.0.10' yuchao只能在10.0.0.10这个客户端登录yuchao@'10.0.0.%' yuchao只能在10.0.0.xx/24网段登录yuchao@'10.0.0.5%' yuchao只能在10.0.0.50~59 登录yuchao@'%' yuchao可以在任意地址登录该mysql服务端yuchao@'db-51' 基于主机名的登录限制
[root@localhost.localdomain /mysql_test]#mysql -uroot -p123456 -e "select User,Host,authentication_string from mysql.user;"mysql: [Warning] Using a password on the command line interface can be insecure.+---------------+-----------+-------------------------------------------+| User | Host | authentication_string |+---------------+-----------+-------------------------------------------+| root | localhost | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 || mysql.session | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE || mysql.sys | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE || ningbo | localhost | *CE257D70249031FEB9EE9D908E2C87A1CFE7155C |+---------------+-----------+-------------------------------------------+
create user chaoge01@'localhost'; # 创建用户无密码select user,host,authentication_string from mysql.user; # 查询创建且设置密码create user chaoge02@'localhost' identified by '123';
alter user chaoge01@'localhost' identified by '123';alter user chaoge01@'localhost' identified by 'yuchaoge666';
[root@db-51 ~]#[root@db-51 ~]#mysql -uchaoge02 -p123mysql: [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 27Server version: 5.7.28 MySQL Community Server (GPL)Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.mysql> set password=password('chaoge666');Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> drop user chaoge02@'localhost';Query OK, 0 rows affected (0.00 sec)
mysql> show privileges;+-------------------------+---------------------------------------+-------------------------------------------------------+| Privilege | Context | Comment |+-------------------------+---------------------------------------+-------------------------------------------------------+| Alter | Tables | To alter the table || Alter routine | Functions,Procedures | To alter or drop stored functions/procedures || Create | Databases,Tables,Indexes | To create new databases and tables || Create routine
查看所有文档mysql> help grant;语法grant all privileges on *.* to 'yuchaoit'@'10.0.0.%' identifiedy by 'chaoge666';授权 权限 库.表 用户@网段 密码
privileges
on
*
.
*
to
'root'@'%'
identified
by
'123456'
;
mysql> show grants for ningbo@'localhost';+-----------------------------------------------------+| Grants for ningbo@localhost |+-----------------------------------------------------+| GRANT ALL PRIVILEGES ON *.* TO 'ningbo'@'localhost' |+-----------------------------------------------------+1 row in set (0.00 sec)
# 移除删除权限revoke delete on *.* from dev01@'10.0.0.%';# 移除所有权限，针对crm这个库revoke all on crm.* from dev01@'10.0.0.%';再次查询show grants for dev01@'10.0.0.%';
mysql> show grants for dev01@'10.0.0.%';+------------------------------------------+| Grants for dev01@10.0.0.% |+------------------------------------------+| GRANT USAGE ON *.* TO 'dev01'@'10.0.0.%' |+------------------------------------------+1 row in set (0.00 sec)
1.停止服务[root@db-51 ~]#systemctl stop mysql2.跳过授权表，免密运行mysqld服务端[root@db-51 ~]#mysqld_safe --skip-grant-tables --user=mysql2022-07-20T09:45:54.661054Z mysqld_safe Logging to '/mysql_test/mysql_3306/db-51.err'.2022-07-20T09:45:54.684950Z mysqld_safe Starting mysqld daemon with databases from /mysql_test/mysql_33063.改密码只能用update语句mysql> update mysql.user set authentication_string=password('ningbo666') where user='root' and host='localhost';Query OK, 1 row affected, 1 warning (0.00 sec)Rows matched: 1 Changed: 1 Warnings: 14.停止mysqlmysql> shutdown;Query OK, 0 rows affected (0.00 sec)5.重启mysqlsystemctl start mysql再次登录[root@db-51 ~]#mysql -uroot -pwww.yuchaoit.cn授权表参数，会导致任意客户端，都可以免密直接登录，务必要记住要删掉
DDL数据定义，全称为Data Definition LanguageDQL数据查询语言，DQL，全称为Data Query LanguageDCL授权控制,全称为Data Control LanguageDML数据操作语言，DML，全称为Data Manipulation Language，中文为数据操作语言
# 登录数据库[root@mysql-server56 ~]#[root@mysql-server56 ~]# mysql -S /data/3307/mysql.sockERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)[root@mysql-server56 ~]# mysql -pyuchao7777 -S /data/3307/mysql.sockWarning: 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 4Server version: 5.6.40-log MySQL Community Server (GPL)Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.# 这句SQL翻译是从mysql库下的user表，查询user、host字段的数据、且对user排序# 这就像我们用excel一样mysql> select user,host from mysql.user order by user;+------+----------------+| user | host |+------+----------------+| | localhost || | mysql-server56 || root | localhost || root | mysql-server56 || root | 127.0.0.1 || root | ::1 || root | % |+------+----------------+7 rows in set (0.00 sec)
# 删除这些无用的用户# 发现删除了2个空用户mysql> delete from mysql.user where user=' ';Query OK, 2 rows affected (0.00 sec)mysql> delete from mysql.user where user='root' and host='mysql-server56';Query OK, 1 row affected (0.00 sec)mysql> delete from mysql.user where user='root' and host='::1';Query OK, 1 row affected (0.00 sec)# 剩下来的用户mysql> select user,host from mysql.user order by user;+------+-----------+| user | host |+------+-----------+| root | localhost || root | 127.0.0.1 || root | % |+------+-----------+3 rows in set (0.00 sec)
mysql> create user pyyu@'%' identified by 'pyyu668';Query OK, 0 rows affected (0.01 sec)# 普通用户被创建的时候，默认有USAGE权限，只能用于登录数据库，无其他权限mysql> show grants for pyyu@'%';+-----------------------------------------------------------------------------------------------------+| Grants for pyyu@% |+-----------------------------------------------------------------------------------------------------+| GRANT USAGE ON *.* TO 'pyyu'@'%' IDENTIFIED BY PASSWORD '*4896EFB7643861D41A6BF9CD6FF012B785508323' |+-----------------------------------------------------------------------------------------------------+1 row in set (0.00 sec)# 只给pyyu用户查看luffy数据库的查询权限mysql> grant select on luffy.* to 'pyyu'@'%' with grant option;Query OK, 0 rows affected (0.00 sec)# 查询该用户的权限mysql> show grants for pyyu@'%';+-----------------------------------------------------------------------------------------------------+| Grants for pyyu@% |+-----------------------------------------------------------------------------------------------------+| GRANT USAGE ON *.* TO 'pyyu'@'%' IDENTIFIED BY PASSWORD '*4896EFB7643861D41A6BF9CD6FF012B785508323' || GRANT SELECT ON `luffy`.* TO 'pyyu'@'%' WITH GRANT OPTION |+-----------------------------------------------------------------------------------------------------+2 rows in set (0.00 sec)
# 这句SQL意为创建luffy数据库，且设置数据库编码为utf8，且大小写不敏感，a和A一样处理mysql> create database if not exists luffy default charset utf8 collate utf8_general_ci;Query OK, 1 row affected (0.01 sec)mysql> show databases;+--------------------+| Database |+--------------------+| information_schema || luffy || mysql || performance_schema || test |+--------------------+5 rows in set (0.01 sec)
mysql> ? Data DefinitionYou asked for help about help category: "Data Definition"For more information, type 'help <item>', where <item> is one of the followingtopics: ALTER DATABASE ALTER EVENT ALTER FUNCTION ALTER LOGFILE GROUP ALTER PROCEDURE ALTER SERVER ALTER TABLE ALTER TABLESPACE ALTER VIEW CONSTRAINT CREATE DATABASE CREATE EVENT CREATE FUNCTION CREATE INDEX CREATE LOGFILE GROUP CREATE PROCEDURE CREATE SERVER CREATE TABLE CREATE TABLESPACE CREATE TRIGGER CREATE VIEW DROP DATABASE DROP EVENT DROP FUNCTION DROP INDEX DROP PROCEDURE DROP SERVER DROP TABLE DROP TABLESPACE DROP TRIGGER DROP VIEW RENAME TABLE TRUNCATE TABLE
mysql> ? Account ManagementYou asked for help about help category: "Account Management"For more information, type 'help <item>', where <item> is one of the followingtopics: ALTER USER CREATE USER DROP USER GRANT RENAME USER REVOKE SET PASSWORD
mysql> ? Data ManipulationYou asked for help about help category: "Data Manipulation"For more information, type 'help <item>', where <item> is one of the followingtopics: CALL DELETE DO DUAL HANDLER INSERT INSERT DELAYED INSERT SELECT JOIN LOAD DATA LOAD XML REPLACE SELECT UNION UPDATE
mysql> show databases;+--------------------+| Database |+--------------------+| information_schema || mysql || performance_schema || sys |+--------------------+4 rows in set (0.00 sec)mysql> show create database mysql;+----------+------------------------------------------------------------------+| Database | Create Database |+----------+------------------------------------------------------------------+| mysql | CREATE DATABASE `mysql` /*!40100 DEFAULT CHARACTER SET latin1 */ |+----------+------------------------------------------------------------------+1 row in set (0.00 sec)
# 查看mysql支持哪些字符集mysql> show character set;# 查看当前mysql服务端，默认是什么字符集# 字符集（character set）：定义了字符以及字符的编码。mysql> show variables like '%char%';+--------------------------+----------------------------------+| Variable_name | Value |+--------------------------+----------------------------------+| character_set_client | utf8 || character_set_connection | utf8 || character_set_database | latin1 || character_set_filesystem | binary || character_set_results | utf8 || character_set_server | latin1 || character_set_system | utf8 || character_sets_dir | /opt/mysql5.7.28/share/charsets/ |+--------------------------+----------------------------------+8 rows in set (0.00 sec)# 查看mysql默认的字符序# 字符序（collation）：定义了字符的比较规则。mysql> show variables like 'collation%';+----------------------+-------------------+| Variable_name | Value |+----------------------+-------------------+| collation_connection | utf8_general_ci || collation_database | latin1_swedish_ci || collation_server | latin1_swedish_ci |+----------------------+-------------------+3 rows in set (0.00 sec)
mysql> desc mysql.user;+------------------------+-----------------------------------+------+-----+-----------------------+-------+| Field | Type | Null | Key | Default | Extra |+------------------------+-----------------------------------+------+-----+-----------------------+-------+| Host | char(60) | NO | PRI | | || User | char(32) | NO | PRI | | || Select_priv | enum('N','Y') | NO | | N | || Insert_priv | enum(
完整写法（可以解决数据库已存在的冲突）create database if not exists kings default charset utf8mb4 ;基本写法create database yuchao_linux charset utf8;create database yuchao_linux charset utf8mb4;
mysql> show databases;+--------------------+| Database |+--------------------+| information_schema | # 系统自带库，存储数据库内置信息| kings || luffy || mysql | # 自带库，存储用户信息，授权等| performance_schema | # 存储和性能相关的数据| test | +--------------------+# 查看show create帮助信息help show create database具体用法，查看建库的语句，可以复用show create database yuchao_linux;show create database kings;
mysql> show create database yuchao_linux;+--------------+-----------------------------------------------------------------------+| Database | Create Database |+--------------+-----------------------------------------------------------------------+| yuchao_linux | CREATE DATABASE `yuchao_linux` /*!40100 DEFAULT CHARACTER SET utf8 */ |+--------------+-----------------------------------------------------------------------+1 row in set (0.00 sec)# 修改数据库编码mysql> alter database yuchao_linux charset utf8mb4;Query OK, 1 row affected (0.00 sec)mysql> show create database yuchao_linux;+--------------+--------------------------------------------------------------------------+| Database | Create Database |+--------------+--------------------------------------------------------------------------+| yuchao_linux | CREATE DATABASE `yuchao_linux` /*!40100 DEFAULT CHARACTER SET utf8mb4 */ |+--------------+--------------------------------------------------------------------------+1 row in set (0.00 sec)
[root@db-51 ~]#cat /etc/my.cnf [mysqld]character_set_server=utf8mb4port=3306user=mysqlbasedir=/opt/mysqldatadir=/mysql_test/mysql_3306socket=/tmp/mysql.sock[mysql]socket=/tmp/mysql.sock重启[root@db-51 ~]#systemctl restart mysql
mysql> create database kings_linux;Query OK, 1 row affected (0.00 sec)mysql> show create database kings_linux;+-------------+-------------------------------------------------------------------------+| Database | Create Database |+-------------+-------------------------------------------------------------------------+| kings_linux | CREATE DATABASE `kings_linux` /*!40100 DEFAULT CHARACTER SET utf8mb4 */ |+-------------+-------------------------------------------------------------------------+1 row in set (0.00 sec)
1.创建表 a.表名的规范 不得数字开头 见名知意，表和业务有关，参考wordpress等数据库命名规范，如yuchaoit_post 不得大写字母 别超过18个字符 别是mysql默认关键字 b. 默认存储引擎是innodb c. 5.7之后，数据表字符集，默认utf8mb4 d. 数据表的字段（列）也和业务有关，字符别超过18个 e. 数据表的字段（列）要选择合适的、足够容量、省磁盘空间的数据类型 f. 数据表的字段（列）建议都not null g. 数据表的字段（列）建议都要有注释 h. 数据表的字段（列） 每张数据表都建议加主键 i. 针对not null的列，一般给与默认值 j. 给每张表添加注释
mysql> create database d1;Query OK, 1 row affected (0.00 sec)mysql> show create database d1;+----------+---------------------------------------------------------------+| Database | Create Database |+----------+---------------------------------------------------------------+| d1 | CREATE DATABASE `d1` /*!40100 DEFAULT CHARACTER SET latin1 */ |+----------+---------------------------------------------------------------+1 row in set (0.00 sec)mysql> # 该特殊符号 /*!40100 是mysql为了兼容不同版本的字符集，自动生成的一个注释信息，忽略即可，不需要你去写
help create table;CREATE TABLE `students` ( `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '学号', `name` varchar(64) NOT NULL COMMENT '学生姓名', `age` tinyint(3) NOT NULL DEFAULT '0' COMMENT '学生年龄', `gender` enum('男','女') NOT NULL COMMENT '学生性别', `address` enum('北京','深圳','云南','江苏') NOT NULL COMMENT '省份', `intime` datetime NOT NULL COMMENT '入学时间', `phone` char(11) NOT NULL COMMENT '手机号', PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
mysql> create table stu_back like students;Query OK, 0 rows affected (0.00 sec)mysql> show tables;+------------------------+| Tables_in_yuchao_linux |+------------------------+| stu_back || students |+------------------------+2 rows in set (0.05 sec)mysql> desc stu_back;+---------+-----------------------------------+------+-----+---------+----------------+| Field | Type | Null | Key | Default | Extra |+---------+-----------------------------------+------+-----+---------+----------------+| id | int(11) | NO | PRI | NULL | auto_increment || name | varchar(64) | NO | | NULL | || age | tinyint(3) | NO | | 0 | || gender | enum('男','女') | NO | | NULL | || address | enum('北京','深圳','云南','江苏') | NO | | NULL | || intime | datetime | NO | | NULL | || phone | char(11) | NO | | NULL | |+---------+-----------------------------------+------+-----+---------+----------------+7 rows in set (0.03 sec)
# 给students表添加一个兴趣字段，hobby 要求只能输入20个汉字以内（20）mysql> desc yuchao_linux.students;+---------+-------------------------------------------+------+-----+---------+----------------+| Field | Type | Null | Key | Default | Extra |+---------+-------------------------------------------+------+-----+---------+----------------+| id | int(11) | NO | PRI | NULL | auto_increment || name | varchar(64) | NO | | NULL | || age | tinyint(3) | NO | | 0 | || gender | enum('男','女') | NO | | NULL | || address | enum('北京','深圳','云南','江苏') | NO | | NULL | || intime | datetime | NO | | NULL | || phone | char(11) | NO | | NULL | |+---------+-------------------------------------------+------+-----+---------+----------------+7 rows in set (0.00 sec)语法：ALTER TABLE 表名 ADD COLUMN 字段名 数据类型 字段约束 注释案例mysql> alter table yuchao_linux.students add column hobby char(20) not null default '学习' comment '学生爱好';Query OK, 0 rows affected (0.01 sec)Records: 0 Duplicates: 0 Warnings: 0
mysql> alter table yuchao_linux.students add column height float(4,1) default null comment '学生身高' first;Query OK, 0 rows affected (0.01 sec)Records: 0 Duplicates: 0 Warnings: 0mysql> desc yuchao_linux.students;+---------+-------------------------------------------+------+-----+---------+----------------+| Field | Type | Null | Key | Default | Extra |+---------+-------------------------------------------+------+-----+---------+----------------+| height | float(4,1) | YES | | NULL | || id | int(11) | NO | PRI | NULL | auto_increment || name | varchar(64) | NO | | NULL | || age | tinyint(3) | NO | | 0 | || gender | enum('男','女') | NO | | NULL | || address | enum('北京','深圳','云南','江苏') | NO | | NULL | || intime | datetime | NO | | NULL | || phone | char(11) | NO | | NULL | || hobby | char(20) | NO | | 学习 | |+---------+-------------------------------------------+------+-----+---------+----------------+9 rows in set (0.00 sec)
mysql> alter table yuchao_linux.students drop height;Query OK, 0 rows affected (0.01 sec)Records: 0 Duplicates: 0 Warnings: 0在性别后面添加 height身高字段alter table yuchao_linux.students add column height float(4,1) default null comment '学生身高' after gender;
删除字段mysql> mysql> alter table yuchao_linux.students drop hobby;Query OK, 0 rows affected (0.00 sec)Records: 0 Duplicates: 0 Warnings: 0危险操作，不能随便删除数据
mysql> alter table students rename to new_students;Query OK, 0 rows affected (0.00 sec)mysql> show tables;+------------------------+| Tables_in_yuchao_linux |+------------------------+| new_students || stu_back |+------------------------+2 rows in set (0.00 sec)
alter table stu_back change phone phone_num varchar(11) not null default '0' comment '手机号';
修改字段的数据类型就是把字段的数据类型转换成另一种数据类型。在 MySQL 中修改字段数据类型的语法规则如下：ALTER TABLE <表名> MODIFY <字段名> <数据类型>其中：表名：指要修改数据类型的字段所在表的名称；字段名：指需要修改的字段；数据类型：指修改后字段的新数据类型。修改telephone_num的数据类型，改为char(11)注意，如果其他属性，不希望改变，也要加上，否则就丢失了mysql> alter table stu_back modify telephone_num char(11) not null;Query OK, 0 rows affected (0.01 sec)Records: 0 Duplicates: 0 Warnings: 0
mysql> drop table stu_back;Query OK, 0 rows affected (0.00 sec)
mysql> ? Data ManipulationYou asked for help about help category: "Data Manipulation"For more information, type 'help <item>', where <item> is one of the followingtopics: CALL DELETE DO DUAL HANDLER INSERT INSERT DELAYED INSERT SELECT JOIN LOAD DATA LOAD XML REPLACE SELECT UNION UPDATE
先看看表字段mysql> desc new_students;mysql> desc new_students;+---------+-------------------------------------------+------+-----+---------+----------------+| Field | Type | Null | Key | Default | Extra |+---------+-------------------------------------------+------+-----+---------+----------------+| id | int(11) | NO | PRI | NULL | auto_increment || name | varchar(64) | NO | | NULL | || age | tinyint(3) | NO | | 0 | || gender | enum('男','女') | NO | | NULL | || height | float(4,1) | YES | | NULL | || address | enum('北京','深圳','云南','江苏') | NO | | NULL | || intime | datetime | NO | | NULL | || phone | char(11) | NO | | NULL | || hobby | char(20) | NO | | 学习 | |+---------+-------------------------------------------+------+-----+---------+----------------+9 rows in set (0.00 sec)
insert into new_students(id,name,age,GENDER,HEIGHT,address,intime,phone,hobby) values(1,'杨松麟',22,'男','188.9','北京','2022-2-24','666666','说段子')查数据mysql> select * from new_students;+----+-----------+-----+--------+--------+---------+---------------------+--------+-----------+| id | name | age | gender | height | address | intime | phone | hobby |+----+-----------+-----+--------+--------+---------+---------------------+--------+-----------+| 1 | 杨松麟 | 22 | 男 | 188.9 | 北京 | 2022-02-24 00:00:00 | 666666 | 说段子 |+----+-----------+-----+--------+--------+---------+---------------------+--------+-----------+1 row in set (0.00 sec)
没写的字段，会填入默认值datetime没有默认值，必须传入id字段一般可以不写，会自动的增长+1确保插入的字段，和值的顺序，是对应上的就行char长度不能超出，否则报错，可以修改配置文件解决该问题插入数据insert into new_students(name,phone,age,intime) value("陈亮亮","123456789",21,"2022-2-23");检查mysql> mysql> select * from new_students;+----+-----------+-----+--------+--------+---------+---------------------+-----------+-----------+| id | name | age | gender | height | address | intime | phone | hobby |+----+-----------+-----+--------+--------+---------+---------------------+-----------+-----------+| 1 | 杨松麟 | 22 | 男 | 188.9 | 北京 | 2022-02-24 00:00:00 | 666666 | 说段子 || 2 | 陈亮亮 | 21 | 男 | NULL | 北京 | 2022-02-23 00:00:00 | 123456789 | 学习 |+----+-----------+-----+--------+--------+---------+---------------------+-----------+-----------+2 rows in set (0.00 sec)
mysql> help Functions;You asked for help about help category: "Functions"For more information, type 'help <item>', where <item> is one of the followingcategories: Bit Functions Comparison Operators Control Flow Functions Date and Time Functions Encryption Functions GROUP BY Functions and Modifiers Information Functions Locking Functions Logical Operators Miscellaneous Functions Numeric Functions Spatial Functions String Functions
# 当前在那个库下mysql> select database();+------------+| database() |+------------+| world |+------------+1 row in set (0.00 sec)# 当前系统时间mysql> select now();+---------------------+| now() |+---------------------+| 2022-07-21 05:10:23 |+---------------------+1 row in set (0.00 sec)# 当前连接的用户信息mysql> select user();+----------------+| user() |+----------------+| root@localhost |+----------------+1 row in set (0.00 sec)# MySQL CONCAT()函数需要一个或多个字符串参数，并将它们连接成一个字符串。mysql> select concat('www.','yuchaoit','.com');+----------------------------------+| concat('www.','yuchaoit','.com') |+----------------------------------+| www.yuchaoit.com |+----------------------------------+1 row in set (0.00 sec)# 实际应用concat函数mysql> select concat('mysql用户：',user,'@',host) from mysql.user;+----------------------------------------+| concat('mysql用户：',user,'@',host) |+----------------------------------------+| mysql用户：dev01@10.0.0.% || mysql用户：yuchao02@10.0.0.% || mysql用户：bob01@10.0.0.7 || mysql用户：chaoge01@localhost || mysql用户：mysql.session@localhost || mysql用户：mysql.sys@localhost || mysql用户：root@localhost || mysql用户：yuchao01@localhost |+----------------------------------------+8 rows in set (0.00 sec)mysql>
select 列from 表where 条件group by 列having 条件order by 列limit 条件
mysql> use world;mysql> select * from city;mysql> select ID,Name,CountryCode,District,Population from city;
5000000
;
语法1select * from city where CountryCode='chn' or CountryCode='usa'语法2select * from city where CountryCode in ('chn','usa')
select
*
from city where Population >=2000000 and Population<=5000000
解法2
select * from city where Population BETWEEN 2000000 and 5000000
select * from city where CountryCode IN ('CHN','USA') and Population > 5000000;select * from city where (CountryCode='CHN' or CountryCode='USA') and Population>5000000
# select sum(Population) from city where CountryCode='CHN' #175953614
# 统计中国每个省的城市个数，和城市名 # district 省select District as 省,count(name) as 城市数量 ,GROUP_CONCAT(name) as 城市名列表 from city where CountryCode='CHN' group by District
)
*
from city where
CountryCode
=
'CHN'
order
by
Population
desc
limit
10
;
# Limit 起点,条数;写法 1select * from city where CountryCode='CHN' order by Population desc limit 5,5写法2select * from city where CountryCode='CHN' order by Population desc limit 5 offset 5
SHOW语句有许多形式，提供关于服务器的数据库、表、列或状态信息的信息。SHOW语法格式：SHOW 关键字 LIKE 'pattern'如果对于一个给定的说明语句的语法包括像'模式'，'模式'是一个字符串，可以包含“%”和“_“通配符。该模式是有用的限制语句输出匹配的值。本节介绍以下：SHOW DATABASES – 显示当前所有数据库的名称SHOW TABLES FROM db_name – 显示数据库中的所有表SHOW ENGINES – 显示MySQL当前支持哪些存储引擎和默认存储引擎SHOW CHARACTER SET – 显示MySQL当前支持哪些字符集SHOW COLLATION – 显示MySQL支持字符集的排序规则SHOW BINARY | MASTER – 显示二进制文件以及文件大小（需要开启二进制日志记录功能SHOW BINLOG EVENTS – 显示二进制文件的执行过程SHOW COLUMNS – 显示表的列信息（等同于DESC，需要先创建表）SHOW CREATE DATABASES – 显示已经创建的库，创建时的语句SHOW CREATE TABLE – 显示已经创建的表，创建时的语句SHOW CREATE FUNTCION – 显示已经创建的函数，创建时的语句SHOW CREATE PROCEDURE – 显示已经创建的存储过程，创建时的语句SHOW CREATE TRIGGER - 显示已经创建的触发器，创建时的语句SHOW CREATE VIEW – 显示已经创建的视图，创建时的语句SHOW CREATE EVENTS – 显示已经创建的事件，创建时的语句SHOW ENGINE – 显示存储引擎的详细信息SHOW WARNINGS – 显示最后一个执行语句所产生的警告信息SHOW ERRORS – 显示最后一个执行语句所产生的错误信息SHOW EVENTS – 显示事件信息SHOW GRANTS – 显示一个用户所拥有的权限SHOW PROCESSLIST – 显示系统中正在运行的所有进程，普通用户只能查看自己的进行信息SHOW PRIVILEGES – 显示MySQL所支持的所有权限，及权限可操作的对象SHOW PLUGINS – 显示MySQL插件信息SHOW MASTER STATUS – 显示Master当前正在使用的二进制信息SHOW TABLE STATUS – 显示表属性信息SHOW INDEX – 显示表索引信息SHOW PROCEDURE STATUS – 显示存储过程信息SHOW FUNCTION STATUS – 显示存储函数信息SHOW TRIGGERS – 显示触发器信息SHOW PROFILE and SHOW PROFILES – 显示执行语句的资源使用情况SHOW SLAVE HOSTS – 显示Master主机上已注册的复制主机列表SHOW SLAVE STATUS – 显示Slave主机状态信息SHOW GLOBAL | SESSION STATUS – 显示MySQL状态变量信息SHOW GLOBAL | SESSION VARIABLES – 显示MySQL系统变量信息
# 查看中国城市的人口# select name,Population from city where CountryCode='CHN';# 查看黑龙江省人口信息-- select name,Population from city where CountryCode='CHN' and District='heilongjiang'# 查看人口数量大于1000000的城市-- select * from city where Population >=1000000# 模糊查询，查询城市名，以HE开头的-- select * from city where name like 'HE%'# 查询中国里，名字带有HE的城市信息# 等于要用到一个表的2个字段同时匹配么-- select * from city where CountryCode='CHN' and name like 'HE%'# 查询中国、美国的城市信息-- select * from city where CountryCode='CHN' or CountryCode='USA'# 语法2-- select * from city where CountryCode in ('CHN','USA')
# 1.建库create database school_db;# 2.建表学生表create table students(id int ,name varchar(10));#3. 建表 成绩表create table score(id int, score int);# 4.表结构查看desc students;desc score;# 5.数据插入insert into students values(1,'yuchao'),(2,'zhangsan'),(3,'sanpang');insert into score values(1,66),(2,88),(3,99)# 6.单表数据查询select * from students;select * from score;# 7.连表查询# 查出于超的成绩mysql> select students.name,score.score from students,score where students.id=score.id and students.name='yuchao';+--------+-------+| name | score |+--------+-------+| yuchao | 66 |+--------+-------+1 row in set (0.00 sec)
题目：世界上小于100人口的城市是哪个国家的# 分析人口数量 城市名 国家名# 分析表字段desc city;desc country;desc countrylanguage;# 人口数量city.Population# 城市名city.name# 国家名country.name# 三表关联的信息# 结果# 查阅人口数小于1000的城市名，国家名select city.Population as 人口数 ,city.name as 城市名 ,country.name as 国家名 from city,country where city.CountryCode=country.code and city.Population<1000# 以人口数，降序排序select city.Population as 人口数 ,city.name as 城市名 ,country.name as 国家名 from city,country where city.CountryCode=country.code and city.Population<1000 order by city.Population desc
# 需求，查阅人口数小于100的城市在什么国家，以及说什么语言？# 分析人口数、城市名、国家名、语言city.Populicationcity.namecountry.namecountryLanguage.language# 找出表关联字段city.CountryCodecountry.code#结果mysql> mysql> select city.Population,city.name,country.name,countrylanguage.`Language` from city,country,countrylanguage where city.CountryCode=country.code and country.code=countrylanguage.CountryCode and city.Population<100;+------------+-----------+----------+-------------+| Population | name | name | Language |+------------+-----------+----------+-------------+| 42 | Adamstown | Pitcairn | Pitcairnese |+------------+-----------+----------+-------------+1 row in set (0.00 sec)
但是这种方式效率并不高，以SQL导出，在海量数据下，例如几十G的场景，备份、恢复的时间都会过长。
因此还会有其他备份方案。
-u mysql用户名
-p mysql用户密码
-S mysql本地socket文件
-h 指定主机地址
-P 指定mysql端口
---database，-B转储几个数据库。通常情况，mysqldump将命令行中的第1个名字参量看作数据库名，后面的名看作表名。使用该选项，它将所有名字参量看作数据库名。备份命令，尽量携带-B参数，会让sql更加完整-B可以跟上多个数据库名，同时备份多个库尽量结合gzip命令压缩指定备份库，以及所有数据[root@db-51 /opt]#mysqldump -uroot -p123456 -B world employees > /mysql_backup/world_employess.sqlmysqldump: [Warning] Using a password on the command line interface can be insecure.
# 备份salaries工资表root@db-51 /opt]#mysqldump -uroot -p123456 employees salaries > /mysql_backup/employees_salaries.sqlmysqldump: [Warning] Using a password on the command line interface can be insecure.[root@db-51 /opt]#[root@db-51 /opt]#ll /mysql_backup/ -htotal 433M-rw-r--r-- 1 root root 162M Jul 21 17:37 all_db.sql-rw-r--r-- 1 root root 111M Jul 21 17:40 employees_salaries.sql-rw-r--r-- 1 root root 161M Jul 21 17:36 world_employess.sql
# 备份库下的多个表[root@db-51 /opt]#mysqldump -uroot -p123456 world city country > /mysql_backup/world_city_country.sqlmysqldump: [Warning] Using a password on the command line interface can be insecure.[root@db-51 /opt]#[root@db-51 /opt]#ll /mysql_backup/ -htotal 434M-rw-r--r-- 1 root root 162M Jul 21 17:37 all_db.sql-rw-r--r-- 1 root root 111M Jul 21 17:40 employees_salaries.sql-rw-r--r-- 1 root root 214K Jul 21 17:48 world_city_country.sql-rw-r--r-- 1 root root 161M Jul 21 17:36 world_employess.sql
有些情况下会只需要表结构，不要数据，命令如下--no-data，-d不写表的任何行信息。如果你只想转储表的结构这很有用。# 备份world库下，所有的表结构mysqldump -uroot -p123456 -d world > /mysql_backup/world_all_table_no_data.sql# 只有建表语句而已了grep -Ev '#|\*|--|^$' /mysql_backup/world_all_table_no_data.sql# 单独备份某个表的结构mysqldump -uroot -pwww.yuchaoit.cn -d world city > /mysql_backup/world_city_no_data.sqlgrep -Ev '#|\*|--|^$' /mysql_backup/world_city_no_data.sql
--no-create-info，-t不写重新创建每个转储表的CREATE TABLE语句。# 只要city表的数据mysqldump -uroot -pwww.yuchaoit.cn -t world city > /mysql_backup/world_city_only_data.sqlgrep -Ev '#|\*|--|^$' /mysql_backup/world_city_only_data.sql
mysql> select @@log_bin;+-----------+| @@log_bin |+-----------+| 0 |+-----------+1 row in set (0.00 sec)
mysql> show variables like '%log_bin%';+---------------------------------+-------+| Variable_name | Value |+---------------------------------+-------+| log_bin | OFF || log_bin_basename | || log_bin_index | || log_bin_trust_function_creators | OFF || log_bin_use_v1_row_events | OFF || sql_log_bin | ON |+---------------------------------+-------+6 rows in set (0.00 sec)
mysql> select @@log_bin_basename;+--------------------+| @@log_bin_basename |+--------------------+| NULL |+--------------------+1 row in set (0.00 sec)mysql> select @@server_id;+-------------+| @@server_id |+-------------+| 0 |+-------------+1 row in set (0.01 sec)
[root@db-51 /mysql_backup]#mkdir /mysql_test/mysql_3306/logs/[root@db-51 /mysql_backup]#chown -R mysql.mysql /www.yuchaoit.cn/[root@db-51 /mysql_backup]#systemctl restart mysqld[root@db-51 /mysql_backup]#mysql -uroot -p123456mysql: [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 2Server version: 5.7.28-log MySQL Community Server (GPL)Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.mysql> show variables like '%log_bin%';+---------------------------------+--------------------------------------------------+| Variable_name | Value |+---------------------------------+--------------------------------------------------+| log_bin | ON || log_bin_basename | /www.yuchaoit.cn/mysql_3306/logs/mysql-bin || log_bin_index | /www.yuchaoit.cn/mysql_3306/logs/mysql-bin.index || log_bin_trust_function_creators | OFF || log_bin_use_v1_row_events | OFF || sql_log_bin | ON |+---------------------------------+--------------------------------------------------+6 rows in set (0.00 sec)
我们要恢复的逻辑是1. 创建了lol库2. 创建了 tanke fashi表，以及插入了数据3. 删除了lol库，到这截止检查数据，恢复数据mysql> show databases;mysql> show master status;mysql> set sql_log_bin=0;Query OK, 0 rows affected (0.00 sec)mysql> source /tmp/restore-lol.sql;mysql> set sql_log_bin=1;Query OK, 0 rows affected (0.00 sec)mysql> select * from lol.fashi;+------+| id |+------+| 1 || 2 || 3 || 4 || 5 || 11 || 22 || 33 || 44 || 55 |+------+10 rows in set (0.00 sec)就很nice，数据回来了
mysql> show binlog events; #只查看第一个binlog文件的内容mysql> show binlog events in 'mysql-bin.000002';#查看指定binlog文件的内容mysql> show binary logs; #获取binlog文件列表mysql> show master status； #查看当前正在写入的binlog文件
# 1.创建表create table linux0224.bank( name varchar(20), money decimal(20, 2))charset=utf8;# 2.插入数据use linux0224;insert into bank values("于超", 20000),("张飞", 6000);# 3.执行事务，如存取钱的更新，updatebegin;update bank set money=money-3000 where name="于超";update bank set money=money+3000 where name="张飞";commit;# 4.查询价格mysql> select * from bank;+--------+----------+| name | money |+--------+----------+| 于超 | 17000.00 || 张飞 | 9000.00 |+--------+----------+2 rows in set (0.00 sec)
# 故意的SQL写错，查看在一个事务下的SQL执行状态begin;update bank set money=money-3000 where name="于超";update bank ssssssssset money=money+3000 where name="张飞";rollback;# 事务特性，实现了数据正确性，一致性mysql> mysql> select * from bank;+--------+----------+| name | money |+--------+----------+| 于超 | 17000.00 || 张飞 | 9000.00 |+--------+----------+2 rows in set (0.00 sec)
语法规则在 MYSQL 中事务处理主要有两种方法：1）用 BEGIN, ROLLBACK, COMMIT来实现BEGIN 开始一个事务ROLLBACK 事务回滚COMMIT 事务确认mysql> show variables like '%commit%';+-----------------------------------------+-------+| Variable_name | Value |+-----------------------------------------+-------+| autocommit | ON | #| binlog_group_commit_sync_delay | 0 || binlog_group_commit_sync_no_delay_count | 0 || binlog_order_commits | ON || innodb_api_bk_commit_interval | 5 || innodb_commit_concurrency | 0 || innodb_flush_log_at_trx_commit | 1 || slave_preserve_commit_order | OFF |+-----------------------------------------+-------+8 rows in set (0.00 sec)
mysql> show variables like '%GTID%';+----------------------------------+-----------+| Variable_name | Value |+----------------------------------+-----------+| binlog_gtid_simple_recovery | ON || enforce_gtid_consistency | OFF || gtid_executed_compression_period | 1000 || gtid_mode | OFF || gtid_next | AUTOMATIC || gtid_owned | || gtid_purged | || session_track_gtids | OFF |+----------------------------------+-----------+8 rows in set (0.00 sec)
vim /etc/my.cnf 加入[root@db-51 ~]#cat /etc/my.cnf [mysqld]gtid-mode=ONenforce-gtid-consistency=truelog-slave-updates=ONserver_id=51log_bin=/mysql_test/mysql_3306/logs/mysql-bincharacter_set_server=utf8mb4port=3306user=mysqlbasedir=/opt/mysqldatadir=/mysql_test/mysql_3306socket=/tmp/mysql.sock[mysql]socket=/tmp/mysql.sock# 重启systemctl restart mysqld
# 创建库以及查看GTIDmysql> create database gtid_db charset utf8mb4;Query OK, 1 row affected (0.00 sec)mysql> show master status;+------------------+----------+--------------+------------------+----------------------------------------+| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |+------------------+----------+--------------+------------------+----------------------------------------+| mysql-bin.000001 | 338 | | | ae068f82-06bc-11ed-839d-000c29b76f3a:1 |+------------------+----------+--------------+------------------+----------------------------------------+1 row in set (0.00 sec)# 创建表，再看gtidmysql> use gtid_db;Database changedmysql> create table t1(id int);Query OK, 0 rows affected (0.01 sec)mysql> show master status;+------------------+----------+--------------+------------------+------------------------------------------+| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |+------------------+----------+--------------+------------------+------------------------------------------+| mysql-bin.000001 | 506 | | | ae068f82-06bc-11ed-839d-000c29b76f3a:1-2 |+------------------+----------+--------------+------------------+------------------------------------------+1 row in set (0.00 sec)# 插入数据再看gtid状态，主动开启个事务看看# 这个事务下，有3个数据插入动作begin;insert into t1 values(1);insert into t1 values(2);insert into t1 values(3);commit;# 可以看出，3个insert 作为了一个事务记录mysql> show master status;+------------------+----------+--------------+------------------+------------------------------------------+| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |+------------------+----------+--------------+------------------+------------------------------------------+| mysql-bin.000001 | 941 | | | ae068f82-06bc-11ed-839d-000c29b76f3a:1-3 |+------------------+----------+--------------+------------------+------------------------------------------+1 row in set (0.00 sec)# 查看binlog的事件记录show binlog events in 'mysql-bin.000008';
1.删除数据库试试mysql> drop database gtid_db;Query OK, 1 row affected (0.00 sec)# 删除动作也是一个事务mysql> show master status;+------------------+----------+--------------+------------------+------------------------------------------+| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |+------------------+----------+--------------+------------------+------------------------------------------+| mysql-bin.000001 | 1107 | | | ae068f82-06bc-11ed-839d-000c29b76f3a:1-4 |+------------------+----------+--------------+------------------+------------------------------------------+1 row in set (0.00 sec)# 查看binlog的事件mysql> show binlog events in 'mysql-bin.000001'; 2.恢复数据，注意关闭二进制日志的记录set sql_log_bin=0;source /tmp/gtid_t1.sql;set sql_log_bin=1;
RESET MASTER可以用来清除GTID的执行历史，全部清空。危险命令。。删除某个日志清除二进制日志需要BINLOG_ADMIN特权。如果未使用--log-bin选项启动服务器以启用二进制日志记录，则此语句无效。Examples:PURGE BINARY LOGS TO 'mysql-bin.010';PURGE BINARY LOGS BEFORE '2019-04-02 22:46:26';
mysql> flush logs;shell> mysqladmin flush-logsshell> mysql -e "flush logs"shell> mysqldump -F# 自动刷新，max_binlog_size 超过binlog的容量后，自动刷新新日志 默认1G ，单位bytes# https://dev.mysql.com/doc/refman/5.7/en/replication-options-binary-log.html`mysql> select @@max_binlog_size;+-------------------+| @@max_binlog_size |+-------------------+| 1073741824 |+-------------------+1 row in set (0.00 sec)
# 常见做法是备份7天mysql> show variables like '%expire%';+--------------------------------+-------+| Variable_name | Value |+--------------------------------+-------+| disconnect_on_expired_password | ON || expire_logs_days | 0 |+--------------------------------+-------+2 rows in set (0.00 sec)# 设置set GLOBAL expire_logs_days=7;
mysql> show variables like 'general_log%';+------------------+---------------------------------------+| Variable_name | Value |+------------------+---------------------------------------+| general_log | OFF || general_log_file | /www.yuchaoit.cn/mysql_3306/db-51.log |+------------------+---------------------------------------+2 rows in set (0.01 sec)
mysql> set global general_log = on;Query OK, 0 rows affected (0.00 sec)mysql> set global general_log = on;Query OK, 0 rows affected (0.00 sec)mysql> show variables like 'general_log%';# 永久配置，则写入my.cnf即可
[root@mysql-server56 3307]# mysqldump -uroot -p123456 -P3307 -h127.0.0.1 --master-data=1 kings --compact > /data/3307/master-data.sqlWarning: Using a password on the command line interface can be insecure.[root@mysql-server56 3307]#[root@mysql-server56 3307]# mysqldump -uroot -p123456 -P3307 -h127.0.0.1 --master-data=2 kings --compact > /data/3307/master-data.sql2Warning: Using a password on the command line interface can be insecure.[root@mysql-server56 3307]#[root@mysql-server56 3307]#[root@mysql-server56 3307]#[root@mysql-server56 3307]#[root@mysql-server56 3307]# ls /data/3307/master-data.sql*/data/3307/master-data.sql /data/3307/master-data.sql2
[root@mysql-server56 3307]# head -1 /data/3307/master-data.sqlCHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000003', MASTER_LOG_POS=120;[root@mysql-server56 3307]# head -1 /data/3307/master-data.sql2-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000003', MASTER_LOG_POS=120;
# 1.数据准备[root@db-51 ~]#mysql -uroot -p123456mysql: [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 18Server version: 5.7.28-log MySQL Community Server (GPL)Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.mysql> create database yc_linux charset utf8mb4;Query OK, 1 row affected (0.01 sec)mysql> use yc_linux;Database changedmysql> create table t1(id int);Query OK, 0 rows affected (0.00 sec)mysql> insert into t1 values(1),(2),(3);Query OK, 3 rows affected (0.00 sec)Records: 3 Duplicates: 0 Warnings: 0mysql> commit ; Query OK, 0 rows affected (0.00 sec)mysql> show master status;+------------------+----------+--------------+------------------+------------------------------------------+| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |+------------------+----------+--------------+------------------+------------------------------------------+| mysql-bin.000001 | 1901 | | | ae068f82-06bc-11ed-839d-000c29b76f3a:1-8 |+------------------+----------+--------------+------------------+------------------------------------------+1 row in set (0.00 sec)# 其实也会自动提交的
30 -- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=1901;3132 --
查看当前gtid信息[root@db-51 ~]#mysql -uroot -p123456mysql: [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 20Server version: 5.7.28-log MySQL Community Server (GPL)Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.mysql> show master status;+------------------+----------+--------------+------------------+------------------------------------------+| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |+------------------+----------+--------------+------------------+------------------------------------------+| mysql-bin.000001 | 1901 | | | ae068f82-06bc-11ed-839d-000c29b76f3a:1-8 |+------------------+----------+--------------+------------------+------------------------------------------+1 row in set (0.00 sec)mysql> use yc_linux;create table t2(id int);insert into t2 values(66),(77),(88);commit ;# 模拟次日下午有人误删除了数据库drop database yc_linux;
恢复周一全量备份的数据先# 检查全备数据[root@db-51 ~]#ls /mysql_3306_backup/full_db_2022-02-24.sql /mysql_3306_backup/full_db_2022-02-24.sql# 查看gtid的分割点# 数据是从这里开始变化的[root@db-51 ~]#grep 'SET @@GLOBAL.GTID_PURGED=' /mysql_3306_backup/full_db_2022-02-24.sql SET @@GLOBAL.GTID_PURGED='ae068f82-06bc-11ed-839d-000c29b76f3a:1-8';# 查看备份开始时，binlog的位置点2930 -- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=1901;3132 --# 截取日志# 起点# 从全量开始的下一个gtid，或者基于1901的pos值都可以mysql-bin.000001ae068f82-06bc-11ed-839d-000c29b76f3a:1-9# 终点，删库的操作# 由于有了gtid的记录# 直接拿到删库的gtid事务id[root@db-51 ~]#mysql -uroot -pwww.yuchaoit.cn -e "show binlog events in 'mysql-bin.000001'" |grep -B 1 "drop database yc_linux"mysql: [Warning] Using a password on the command line interface can be insecure.mysql-bin.000001 2342 Gtid 51 2407 SET @@SESSION.GTID_NEXT= 'ae068f82-06bc-11ed-839d-000c29b76f3a:11'mysql-bin.000001 2407 Query 51 2511 drop database yc_linux# 日志截取（核心点了）# 拿上一个记录即可，也就是删数据的前一个事务# 恢复gtid的日志范围# 也就是拿到了全量备份，次日的数据变化记录（截止到drop database 之前）mysqlbinlog --skip-gtids --include-gtids='ae068f82-06bc-11ed-839d-000c29b76f3a:9-10' /www.yuchaoit.cn/mysql_3306/logs/mysql-bin.000001 > /mysql_3306_backup/restore_shizhan.sql
mysql> use yc_linux;Database changedmysql> show tables;+--------------------+| Tables_in_yc_linux |+--------------------+| t1 || t2 |+--------------------+2 rows in set (0.00 sec)mysql> select * from t1;+------+| id |+------+| 1 || 2 || 3 |+------+3 rows in set (0.00 sec)mysql> select * from t2;+------+| id |+------+| 66 || 77 || 88 |+------+3 rows in set (0.01 sec)
[root@db-51 ~]#ls -l /mysql_test/mysql_3306/world/total 1016-rw-r----- 1 mysql mysql 8710 Feb 24 15:09 city.frm-rw-r----- 1 mysql mysql 606208 Feb 24 15:09 city.ibd-rw-r----- 1 mysql mysql 9172 Feb 24 15:09 country.frm-rw-r----- 1 mysql mysql 163840 Feb 24 15:09 country.ibd-rw-r----- 1 mysql mysql 8702 Feb 24 15:09 countrylanguage.frm-rw-r----- 1 mysql mysql 229376 Feb 24 15:09 countrylanguage.ibd-rw-r----- 1 mysql mysql 67 Jul 21 2022 db.opt
mysql> show engines;+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+| Engine | Support | Comment | Transactions | XA | Savepoints |+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+| MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO || CSV | YES | CSV storage engine | NO | NO | NO || MyISAM | YES | MyISAM storage engine | NO | NO | NO || BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | NO | NO | NO || PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO || InnoDB | DEFAULT | Supports transactions, row-level locking, and foreign keys | YES | YES | YES || ARCHIVE | YES | Archive storage engine | NO | NO | NO || MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO || FEDERATED | NO | Federated MySQL storage engine | NULL | NULL | NULL |+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+9 rows in set (0.00 sec)
mysql> show variables like '%storage_engine%';+----------------------------------+--------+| Variable_name | Value |+----------------------------------+--------+| default_storage_engine | InnoDB || default_tmp_storage_engine | InnoDB || disabled_storage_engines | || internal_tmp_disk_storage_engine | InnoDB |+----------------------------------+--------+4 rows in set (0.00 sec)
[root@db-51 ~]#mysql -uroot -p123456mysql: [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 10Server version: 5.7.28-log MySQL Community Server (GPL)Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.mysql> create database xtraback_test charset utf8mb4;Query OK, 1 row affected (0.00 sec)mysql> use xtraback_test;Database changedmysql> create table full_t1(id int);Query OK, 0 rows affected (0.00 sec)mysql> insert into full_t1 values(1),(2),(3);Query OK, 3 rows affected (0.00 sec)Records: 3 Duplicates: 0 Warnings: 0mysql> commit ;Query OK, 0 rows affected (0.00 sec)mysql>
# 用户在网页中插入数据了，也是我们在全量备份之后的第一次数据写入，会被binlog记录，实现增量日志[root@db-51 ~]#mysql -uroot -p123456mysql: [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 13Server version: 5.7.28-log MySQL Community Server (GPL)Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.mysql> use xtraback_test;Reading table information for completion of table and column namesYou can turn off this feature to get a quicker startup with -ADatabase changedmysql> mysql> create table inc_t1(id int);Query OK, 0 rows affected (0.01 sec)mysql> insert into inc_t1 values(1),(2),(3);Query OK, 3 rows affected (0.00 sec)Records: 3 Duplicates: 0 Warnings: 0mysql> commit;Query OK, 0 rows affected (0.00 sec)mysql>
[root@db-51 ~]#mysql -uroot -p123456mysql: [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 20Server version: 5.7.28-log MySQL Community Server (GPL)Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.mysql> use xtraback_test;Reading table information for completion of table and column namesYou can turn off this feature to get a quicker startup with -ADatabase changedmysql> create table inc_t2(id int);Query OK, 0 rows affected (0.01 sec)mysql> insert into inc_t2 values(1),(2),(3);Query OK, 3 rows affected (0.00 sec)Records: 3 Duplicates: 0 Warnings: 0mysql> commit;Query OK, 0 rows affected (0.00 sec)
[root@db-51 ~]#mysql -uroot -p123456mysql: [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 20Server version: 5.7.28-log MySQL Community Server (GPL)Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.mysql> use xtraback_test;Reading table information for completion of table and column namesYou can turn off this feature to get a quicker startup with -ADatabase changedmysql> create table inc_t2(id int);Query OK, 0 rows affected (0.01 sec)mysql> insert into inc_t2 values(1),(2),(3);Query OK, 3 rows affected (0.00 sec)Records: 3 Duplicates: 0 Warnings: 0mysql> commit;Query OK, 0 rows affected (0.00 sec)
[root@db-51 ~]#mysql -uroot -p123456mysql: [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 23Server version: 5.7.28-log MySQL Community Server (GPL)Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.mysql> use xtraback_test;Reading table information for completion of table and column namesYou can turn off this feature to get a quicker startup with -ADatabase changedmysql> create table inc_t3(id int);Query OK, 0 rows affected (0.01 sec)mysql> insert into inc_t3 values(1),(2),(3);Query OK, 3 rows affected (0.00 sec)Records: 3 Duplicates: 0 Warnings: 0mysql> commit;Query OK, 0 rows affected (0.00 sec)
[root@db-51 /xtrabackup_data]#mysql -uroot -p123456mysql: [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 26Server version: 5.7.28-log MySQL Community Server (GPL)Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.mysql> use xtraback_test;Reading table information for completion of table and column namesYou can turn off this feature to get a quicker startup with -ADatabase changedmysql> create table inc_t4(id int);Query OK, 0 rows affected (0.00 sec)mysql> insert into inc_t4 values(1),(2),(3);Query OK, 3 rows affected (0.00 sec)Records: 3 Duplicates: 0 Warnings: 0mysql> commit ;Query OK, 0 rows affected (0.00 sec)mysql>
[root@db-51 ~]#mysql -e "show variables like 'log_bin'"+---------------+-------+| Variable_name | Value |+---------------+-------+| log_bin | ON |+---------------+-------+
1.主库设置个密码mysqladmin password 1234562.创建复制用户mysql -uroot -p123456 -e "grant replication slave on *.* to 'repl'@'10.0.0.%' identified by '123456';"3.查看用户mysql -uroot -p123456 -e "select user,host,plugin from mysql.user"4.查看权限mysql -uroot -p123456 -e "show grants for repl@'10.0.0.%'"
# 备份命令# -A 备份所有库表# --single-transaction，给所有数据库加锁，防止数据写入，导致备份错误# --master-data=2 将binlog的信息以注释形式备份# -R 导出mysql自定义函数# -E 导出events事件 # --triggers 导出所有数据表的触发器mysqldump -uroot -p123456 -A --master-data=2 --single-transaction -R -E --triggers --max_allowed_packet=64M > /data/full.sql# 发给slavefor i in 52 53 ;do scp /data/full.sql 10.0.0.$i:/opt/;done
先查看binlog的截止点[root@db-52 ~]#sed -n '19,22p' /opt/full.sql -- Position to start replication or point-in-time recovery from---- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000003', MASTER_LOG_POS=704;
mysql> show processlist;+----+-------------+-----------+------+---------+------+--------------------------------------------------------+------------------+| Id | User | Host | db | Command | Time | State | Info |+----+-------------+-----------+------+---------+------+--------------------------------------------------------+------------------+| 1 | system user | | NULL | Connect | 807 | Waiting for master to send event | NULL || 2 | system user | | NULL | Connect | 742 |
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updatesSlave_IO_State: Waiting for master to send eventMaster_Host: 10.0.0.51Master_User: replMaster_Port: 3306Connect_Retry: 10Master_Log_File: mysql-bin.000004Read_Master_Log_Pos: 485Relay_Log_File: db-53-relay-bin.000005Relay_Log_Pos: 698Relay_Master_Log_File: mysql-bin.000004Slave_IO_Running: YesSlave_SQL_Running: Yes
binlog日志文件
中继日志 relay-log命名方式datadir/hostname-relay-bin.xxxxx[root@db-53 ~]#ls /data/mysql_3306/ -l |grep relay-rw-r----- 1 mysql mysql 373 Jul 29 00:26 db-53-relay-bin.000004-rw-r----- 1 mysql mysql 698 Jul 29 00:27 db-53-relay-bin.000005-rw-r----- 1 mysql mysql 50 Jul 29 00:26 db-53-relay-bin.index-rw-r----- 1 mysql mysql 61 Jul 29 00:27 relay-log.info[root@db-53 ~]#作用：存储获取到的binlog信息
mysql> show slave hosts;+-----------+------+------+-----------+--------------------------------------+| Server_id | Host | Port | Master_id | Slave_UUID |+-----------+------+------+-----------+--------------------------------------+| 53 | | 3306 | 51 | 97630496-0def-11ed-8051-000c291bb766 || 52 | | 3306 | 51 | 96c4afb2-0dee-11ed-bcc5-000c294c7d18 |+-----------+------+------+-----------+--------------------------------------+2 rows in set (0.00 sec)
# 查看从库复制状态show slave status \G# 查看主库连接信息、以及binlog、pos值等mysql> show slave status\G*************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 10.0.0.51 Master_User: repl Master_Port: 3306 Connect_Retry: 10 Master_Log_File: mysql-bin.000004 Read_Master_Log_Pos: 485# 擦看relay-log回放的信息 Relay_Log_File: db-53-relay-bin.000005 Relay_Log_Pos: 698 Relay_Master_Log_File: mysql-bin.000004 Slave_IO_Running: Yes Slave_SQL_Running: Yes# 线程状态信息，这个状态不对，复制就有问题[root@db-53 /data/mysql_3306]#mysql -uroot -p123456 -e "show slave status\G" |grep -E 'IO|SQL'mysql: [Warning] Using a password on the command line interface can be insecure. Slave_IO_State: Waiting for master to send event Slave_IO_Running: Yes Slave_SQL_Running: Yes Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 0 Last_SQL_Error: SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates Last_IO_Error_Timestamp: Last_SQL_Error_Timestamp:# 提取复制相关信息[root@db-53 /data/mysql_3306]#mysql -uroot -p123456 -e "show slave status\G" |grep -E 'Replicate'mysql: [Warning] Using a password on the command line interface can be insecure. Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Replicate_Ignore_Server_Ids: Replicate_Rewrite_DB:# 落后主库的秒数[root@db-53 /data/mysql_3306]#mysql -uroot -p123456 -e "show slave status\G" |grep -E 'Behind'mysql: [Warning] Using a password on the command line interface can be insecure. Seconds_Behind_Master: 0# 关于GTID的信息[root@db-53 /data/mysql_3306]#mysql -uroot -p123456 -e "show slave status\G" |grep -E 'Gtid|Auto'mysql: [Warning] Using a password on the command line interface can be insecure. Retrieved_Gtid_Set: Executed_Gtid_Set: Auto_Position: 0
[root@db-53 /data/mysql_3306]#mysql -uroot -p123456 -e "show slave status\G" |grep 'Relay_Log_'mysql: [Warning] Using a password on the command line interface can be insecure. Relay_Log_File: db-53-relay-bin.000005 Relay_Log_Pos: 698 Relay_Log_Space: 1071
[root@db-53 /data/mysql_3306]#mysql -uroot -p123456 -e "show slave status\G" |grep '_Master'mysql: [Warning] Using a password on the command line interface can be insecure. Read_Master_Log_Pos: 485 Relay_Master_Log_File: mysql-bin.000004 Exec_Master_Log_Pos: 485 Seconds_Behind_Master: 0# 用于计算主从复制的延时日志量
Slave_IO_Running: Yes # IO线程工作状态，yes，no，connection三个状态Slave_SQL_Running: Yes # SQL工作状态，yes ，noLast_IO_Error: # IO故障代码，2003，1045，1040,1593,1236Last_SQL_Errno: 0 # SQL线程故障代码，1008，1007[root@db-53 /data/mysql_3306]#mysql -uroot -p123456 -e "show slave status\G" |grep 'Error'mysql: [Warning] Using a password on the command line interface can be insecure. Last_Error: Last_IO_Error: Last_SQL_Error: Last_IO_Error_Timestamp: Last_SQL_Error_Timestamp:
网络波动master 连接信息错误，如端口，用户，密码，授权规则错误，复制无权限防火墙拒绝请求主机连接数到达上限[root@db-53 /data/mysql_3306]#mysql -uroot -p123456 -e "select @@max_connections;"mysql: [Warning] Using a password on the command line interface can be insecure.+-------------------+| @@max_connections |+-------------------+| 151 |+-------------------+master、slave版本不一，5.7 、8.0、 5.6
mysql> show global variables like '%gtid%';+----------------------------------+----------------------------------------+| Variable_name | Value |+----------------------------------+----------------------------------------+| binlog_gtid_simple_recovery | ON || enforce_gtid_consistency | ON || gtid_executed | 7588844c-0eb4-11ed-bfb7-000c29b76f3a:1 || gtid_executed_compression_period | 1000 || gtid_mode | ON || gtid_owned | || gtid_purged | || session_track_gtids | OFF |+----------------------------------+----------------------------------------+8 rows in set (0.00 sec)mysql> show global variables like 'server%';+----------------+--------------------------------------+| Variable_name | Value |+----------------+--------------------------------------+| server_id | 51 || server_id_bits | 32 || server_uuid | 7588844c-0eb4-11ed-bfb7-000c29b76f3a |+----------------+--------------------------------------+3 rows in set (0.00 sec)mysql> show master status;+------------------+----------+--------------+------------------+----------------------------------------+| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |+------------------+----------+--------------+------------------+----------------------------------------+| mysql-bin.000002 | 444 | | | 7588844c-0eb4-11ed-bfb7-000c29b76f3a:1 |+------------------+----------+--------------+------------------+----------------------------------------+1 row in set (0.00 sec)
# 写入数据mysql> show master status;+------------------+----------+--------------+------------------+----------------------------------------+| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |+------------------+----------+--------------+------------------+----------------------------------------+| mysql-bin.000002 | 444 | | | 7588844c-0eb4-11ed-bfb7-000c29b76f3a:1 |+------------------+----------+--------------+------------------+----------------------------------------+1 row in set (0.00 sec)mysql> mysql> create database happy_yu;Query OK, 1 row affected (0.00 sec)mysql> show master status;+------------------+----------+--------------+------------------+------------------------------------------+| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |+------------------+----------+--------------+------------------+------------------------------------------+| mysql-bin.000002 | 615 | | | 7588844c-0eb4-11ed-bfb7-000c29b76f3a:1-2 |+------------------+----------+--------------+------------------+------------------------------------------+1 row in set (0.00 sec)mysql> create database happy_yu2;Query OK, 1 row affected (0.00 sec)mysql> show master status;+------------------+----------+--------------+------------------+------------------------------------------+| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |+------------------+----------+--------------+------------------+------------------------------------------+| mysql-bin.000002 | 789 | | | 7588844c-0eb4-11ed-bfb7-000c29b76f3a:1-3 |+------------------+----------+--------------+------------------+------------------------------------------+1 row in set (0.00 sec)mysql> show master status\G*************************** 1. row *************************** File: mysql-bin.000002 Position: 789 Binlog_Do_DB: Binlog_Ignore_DB: Executed_Gtid_Set: 7588844c-0eb4-11ed-bfb7-000c29b76f3a:1-31 row in set (0.00 sec)
|
Databases
|
To
use
CREATE
FUNCTION/PROCEDURE
|
| Create temporary tables | Databases | To use CREATE TEMPORARY TABLE |
| Create view | Tables | To create new views |
| Create user | Server Admin | To create new users |
| Delete | Tables | To delete existing rows |
| Drop | Databases,Tables | To drop databases, tables, and views |
| Event | Server Admin | To create, alter, drop and execute events |
| Execute | Functions,Procedures | To execute stored routines |
| File | File access on server | To read and write files on the server |
| Grant option | Databases,Tables,Functions,Procedures | To give to other users those privileges you possess |
| Index | Tables | To create or drop indexes |
| Insert | Tables | To insert data into tables |
| Lock tables | Databases | To use LOCK TABLES (together with SELECT privilege) |
| Process | Server Admin | To view the plain text of currently executing queries |
| Proxy | Server Admin | To make proxy user possible |
| References | Databases,Tables | To have references on tables |
| Reload | Server Admin | To reload or refresh tables, logs and privileges |
| Replication client | Server Admin | To ask where the slave or master servers are |
| Replication slave | Server Admin | To read binary log events from the master |
| Select | Tables | To retrieve rows from table |
| Show databases | Server Admin | To see all databases with SHOW DATABASES |
| Show view | Tables | To see views with SHOW CREATE VIEW |
| Shutdown | Server Admin | To shut down the server |
| Super | Server Admin | To use KILL thread, SET GLOBAL, CHANGE MASTER, etc. |
| Trigger | Tables | To use triggers |
| Create tablespace | Server Admin | To create/alter/drop tablespaces |
| Update | Tables | To update existing rows |
| Usage | Server Admin | No privileges - allow connect only |
