mysql_mariadb实用常用命令集等2018_LCF，
mysql 和mariadb都是同一作者开发的，所以很多命令基本相通，以及其它数据库，命令很多基本相似。
下面是自己花了不少时间整理出来的常用和实用的命令，希望能对大家有所帮助。
一、查看篇
1) 查看当前数据库版本信息
MariaDB [(none)]> select version();（实用）+----------------+
| version() |
+----------------+
| 5.5.44-MariaDB |
+----------------+
2）在命令行查看数据库版本信息
[root@pc66 ~]# mysql --help | grep Distrib
mysql Ver 15.1 Distrib 5.5.44-MariaDB, for Linux (x86_64) using readline 5.1
3) 查看数据库已安装的相关软件(如果是mysql 就把mariadb*换成 mysql*即可）
[root@pc66 ~]# rpm -qa | grep mariadb*
mariadb-devel-5.5.44-2.el7.i686
mariadb-server-5.5.44-2.el7.x86_64
mariadb-5.5.44-2.el7.x86_64
mariadb-libs-5.5.44-2.el7.i686
mariadb-libs-5.5.44-2.el7.x86_64
mariadb-devel-5.5.44-2.el7.x86_64
3) 查看数据库详细信息
MariaDB [(none)]> status;
3）查看数据导入导出安全目录（实用）
MariaDB [(none)]> show variables like "secure_file_priv";
4）查看数据库引擎表（实用）
MariaDB [(none)]> show engines;
5）查看数据库列表（实用）
MariaDB [(none)]> show databases;
6) 查看当前选择的数据库位置（mysql里没有明确显示所选择的库，只能用下面命令查询）
MariaDB [lcf]> select database();
7）查看所选数据数据库详细信息(很实用的命令）
MariaDB [lcf]> show create database mysql;
+----------+------------------------------------------------------------------+
| Database | Create Database |
+----------+------------------------------------------------------------------+
| mysql | CREATE DATABASE `mysql` /*!40100 DEFAULT CHARACTER SET latin1 */ |
+----------+------------------------------------------------------------------+
1 row in set (0.00 sec)
8）查看数据表详细信息（包含结构信息）（实用）
MariaDB [lcf]> show create table a;
+-------+---------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+---------------------------------------------------------------------------------------------------------------------+
| a | CREATE TABLE `a` (
`id` int(3) DEFAULT NULL,
`name` char(8) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-------+---------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
9）查看数据表结构信息（实用）
MariaDB [lcf]> desc a;
+-------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id | int(3) | YES | | NULL | |
| name | char(8) | YES | | NULL | |
+-------+---------+------+-----+---------+-------+
2 rows in set (0.00 sec)
10）查看数据库存入目录
MariaDB [lcf]> show global variables like "%datadir%";
11）查看全局配置变量信息
MariaDB [lcf]> show global variables;
12) 列出MySQL服务器运行各种状态值(性能状态查看）
MariaDB [lcf]> show global status;
13） 慢查询(性能状态查看）
MariaDB [lcf]> show variables like '%slow%';
MariaDB [lcf]> show global status like '%slow%';
配置中打开了记录慢查询，执行时间超过2秒的即为慢查询，系统显示有4148个慢查询，你可以分析慢查询日志，找出有问题的SQL语句，慢查询时间不宜设置过长，否则意义不大，最好在5秒以内，如果你需要微秒级别的慢查询，可以考虑给MySQL打补丁：http://www.percona.com/docs/wiki/release:start，记得找对应的版本。
14）查看最连接数配置(性能状态查看）
MariaDB [lcf]> show variables like 'max_connections';
15) 查看表记录
select * from A;
／／查看全部
select name,id from A;
／／查看指定字段
select name,id from A where id=2;
／／查看满足where后面条件的指定字段记录
二、建库、建表
1）建库
MariaDB [lcf]> create database myip;
2）简易建表
MariaDB [lcf]> create table k1( id int(6), name char(3));
3）建表（主键，索引，自增长，数据类型，字符类型，日期类型，枚举类型,选择引擎，选择字体）
MariaDB [lcf]> create table k8(
-> id int(5) zerofill not null primary key auto_increment,
／／int数值类型，not null不能录入空值，zerofill自动填充0（如录入记录999，会自动补0到5位数变成00999）
／／primary key主键，可以设置多个（要在后面指定，像index那样指定)
／／auto_increment自增长数值－必须是键值才能自增长，像设置成index或primary key
-> name char(12) not null, ／／字符类型
-> sex enum("boy","girl") default "girl", ／／enum(...)枚举单选类型，default ".."设置默认值
->age int, ／／数值或字符类型没指明长度时默认最大长度，最好指定长度可以节省存储空间
-> hobby set("sing","writing","movie"), ／／set(...)枚举多选类型
->indate datetime, ／／日期类型不用指长度，固定长度（date year day time datetime)
-> index(name,datetime) ／／index（字段名..）设置索引值,可以指定一个或多个
-> )engine=innodb default charset=utf8; ／／engine=引擎类型名 设置引擎类型 default charset=字体编号 设置字体
－－－
create table k8( id int(5) zerofill not null primary key auto_increment, name char(12) not null, sex enum("boy","girl") default "girl",age int, hobby set("sing","writing","movie"),indate datetime, index(name) )engine=innodb default charset=utf8;
－－－同上一样语句代码，复制粘贴即可调试。
MariaDB [lcf]> create temporary table abc( id int(2), name char(32));
／／关键字temporary 建立临时表，在退出连接时自动删除,无法在show查看，但可以用select查看，手动删除用drop table 表名。
三、添加、修改、删除表记录、删除表 删除库
１）添加记录
以上面创建的表k8为例
MariaDB [lcf]> desc k8;
+--------+-------------------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------------------------+------+-----+---------+----------------+
| id | int(5) unsigned zerofill | NO | PRI | NULL | auto_increment |
| name | char(12) | NO | MUL | NULL | |
| sex | enum('boy','girl') | YES | | girl | |
| age | int(11) | YES | | NULL | |
| hobby | set('sing','writing','movie') | YES | | NULL | |
| indate | datetime | YES | | NULL | |
+--------+-------------------------------+------+-----+---------+----------------+
6 rows in set (0.00 sec)
／／先查看下表结构,Field列字段名，Type列字段类型，Ｎull列是否能为空记录，
／／key列索引和键值，Default列默认值，Extra列扩展功能
－－－插入记录
MariaDB [lcf]> insert into k8-> values(null,"璐程风","boy",10,("sing,movie"),now()),
／／null指不输入值，使用自增值，除数值类型外日期和字符类型的都要加“”号或''号，输入的数据一定要和设置的类型大小相符合。
-> (3,"王月月","girl",8,("sing"),"20171212121212");
／／可以同时插入一条或多条记录，insert into 表名 values(...）,(...); 使用中文输入法时使用半角符号，英文的默认半角符号。
／／第二记录开头写３指定数值也是可以的，前提是指的数值要不重复且符合类型长度即可。
－－－
insert into k8 values(null,"璐程风","boy",10,("sing,movie"),now()),(3,"王月月","girl",8,("sing"),"20171212121212");
－－－同上一样语句代码，复制粘贴即可调试。
MariaDB [lcf]> select * from k8;
+-------+-----------+------+------+------------+---------------------+
| id | name | sex | age | hobby | indate |
+-------+-----------+------+------+------------+---------------------+
| 0000１ | 王月月 | girl | 8 | sing | 2017-12-12 12:12:12 |
| 0000３ | 璐程风 | boy | 10 | sing,movie | 2018-05-27 13:52:56 |
+-------+-----------+------+------+------------+---------------------+
2 rows in set (0.00 sec)
／／查看插入数据后的表
－－－插入指定字段记录
insert into k8(name,sex) values("蓝莓","girl"), ("星月","boy");
／／上面语句可直接调用，这种插入方式，非空字段一定要写入数据
MariaDB [lcf]> select * from k8;
+-------+-----------+------+------+------------+---------------------+
| id | name | sex | age | hobby | indate |
+-------+-----------+------+------+------------+---------------------+
| 0000１| 王月月 | girl | 8 | sing | 0000-00-00 00:00:00 |
| 0000３ | 璐程风 | boy | 10 | sing,movie | 2018-05-27 14:14:07 |
| 0000４ | 蓝莓 | girl | NULL | NULL | NULL |
| 0000５ | 星月 | boy | NULL | NULL | NULL |
+-------+-----------+------+------+------------+---------------------+
4 rows in set (0.00 sec)
／／查看插入数据后的表
２）修改表记录
MariaDB [lcf]> update k8 set age=12;
／／update 表名 set 字段名=..，字段名=..； 如果后面不加条件，那该字段的所有值会变成一样的数据
MariaDB [lcf]> update k8 set age=12,id=23 where name="星月";
／／加了where条件后，只有条件匹配的行记录才会被更新相应字段值，可以同时更改多个字段值
３）删除表记录、删除表 库
MariaDB [lcf]> delete from k8;
／／不加条件等于记录全删
MariaDB [lcf]> delete from k8 where id=8;
／／指定条件删除匹配的表记录
MariaDB [lcf]> drop table k8,k1,k3;
／／删除表k8 k1 k3
MariaDB [lcf]> drop database myip;
／／册除数据库；
－－－／／删除库和表的方法还有一种就是删除对应的数据库文件目录或表文件即可
３）复制表
MariaDB [lcf]> create table cp1 select * from k8;
／／复制整张表, 但这种复制不能把key值复制过来,只有自己再手动设置key值
MariaDB [lcf]> create table cp1 select * from k8 where 1=3;
／／只复制表结构
四、添加、修改、删除、移位表字段和相关设置，修改表名、库名
１）添加、修改类型属性、删除、移动表字段位置，改字段名、表名(添加、修改、删除可同时进行 用，关键字分隔即可）
alter table 表名 add / modify / drop / change / rename
－－－添加字段／添加相关设置，关键字add
MariaDB [lcf]> alter table k8 add ip int;
／／在k8表中添加新字段ip，会在表最后列添加
MariaDB [lcf]> alter table k8 add ip2 int after id,add ip6 int;
／／在k8表中字段id后添加新字段ip2，关键字after 已有字段，可同时添加多个字段用，add 分隔即可
MariaDB [lcf]> alter table k8 add ip3 int first ;
／／在k8表中第一列添加字段ip3，也就是在最前面，关键字first
MariaDB [lcf]> alter table k10 add index(id,ic),add primary key(name);;
／／在k10表中添加索引和关键字
－－－修改字段类型属性，关键字modify，修改引擎和字体时不用加这个关键字
MariaDB [lcf]> alter table k8 modify ip char(20) default "aa";
／／修改k8表中字段ip类型属性为char(20) default "aa"
／／注：修改类型属性时，要确实已有对应字段的记录和修改后的类型大小等一致才不会报错，最好是空记录时修改。
MariaDB [lcf]> alter table k8 engine=innodb;
MariaDB [lcf]> alter table k8 default charset=utf8;
／／修改引擎和字休
－－－删除字段／删除相关设置，关键字drop
MariaDB [lcf]> alter table k8 drop ip3;
／／删除k8表字段ip3
MariaDB [lcf]> alter table k8 drop index name,drop primary key;
／／删除k8表索引值name并删除主键，drop index 字段名，drop primary key删除主键（如果主键有自增，要先取消才能删除）
－－－移动字段位置，关键字after , first
方式：modiy 字段名 类型属性 after 字段名／first 只要照抄原字段类型属性，再用这两个关键字来移位即可。
MariaDB [lcf]> alter table k90 modify id int after name;
／／将k90表中字段id 移到字段name后面
MariaDB [lcf]> alter table k88 modify id int first;
／／将k88表中字段id 移到最前面
－－－改字段名、改表名，关键字change 字段名 rename 表名
MariaDB [lcf]> alter table k8 change ind idd int(3);
／／改变k8表字段ind名字，改成idd 注：这个改名同时也可以改变类型属性值，只想改名那就重抄原类型属性即可
MariaDB [lcf]> alter table k2 rename kkk;
／／将k2表改名成kkk
－－－注：以上改字段、加字段等可写在一条语句上同时进行，用逗号隔开就行了。
五、移表文件到别的库、改库名
１）移表文件到别的库,关键字rename table 库名.表名 to 库名;
MariaDB [lcf]>rename table lcf.a to kke;
／／将lcf库里的a表移到kke库里
２）改库名
－－－方法１（别外摘要https://www.cnblogs.com/gomysql/p/3584881.html）
1.创建需要改成新名的数据库。
2.mysqldum 导出要改名的数据库
3.删除原来的旧库（确定是否真的需要）
当然这种方法虽然安全，但是如果数据量大,会很耗时和影响性能。
#!/bin/bash
# 假设将sakila数据库名改为new_sakila
# MyISAM直接更改数据库目录下的文件即可
mysql -uroot -p123456 -e 'create database if not exists new_sakila'
list_table=$(mysql -uroot -p123456 -Nse "select table_name from information_schema.TABLES where TABLE_SCHEMA='sakila'")
for table in $list_table
do
mysql -uroot -p123456 -e "rename table sakila.$table to new_sakila.$table"
done这里用到了rename table,改表名的命令，但是如果新表名后面加数据库名，就会将老数据库的表移动到新的数据库，所以，这种方法即安全，又快速。
六、外键的使用
－－外键篇我已经单独写了一篇使用方法 https://blog.csdn.net/cbuy888/article/details/80456010 这里就简单写使用代码了
使用外键的前提：
1. 表储存引擎必须是innodb，否则创建的外键无约束效果。
2. 外键的列类型必须与父表的主键类型完全一致。
3. 外键的名字不能重复(一般使用。
建外键表两种方式(创建表时关联/添加外键)
1) 第一种方式创建表时加外键使用实例:
mysql> create table A( name char(12), id int(8) ,index(id))engine=innodb; //先建立A表,
mysql> create table B( //建立B表,同时做外键
-> id int(9),
-> money int(9),
-> index(id),
-> foreign key(id) references A (id) //这个是必加项,foreign key(B表要关联的字段),references A表名 (对应字段)
-> on delete cascade on update cascade //这里是可选项的,只加一项或都不加都可以的,看需求.
-> )engine=innodb; //这个必须是innodb类型,并且和A表的一致
第二方式在已有的表上做和A表关联的外键(最好是新建好没有记录的,不然会因为记录对不上而创建不成功)
mysql> create table C( //这里先创建一个空表C
-> id int(7),
-> money int(5),
-> index(id)
-> )engine=innodb;
mysql> alter table Cadd constraint abc //在C表上添加和表A关联的外键,constraint 外键名(自己任意取)
-> foreign key(id) references A(id) //和创建时一样的输入
-> on delete cascade on update cascade; //一样是可选项,这行不写也可以通过.
注: @以上的cascade是上面介强的四种模式之一,是可以替换成其它模式的,如写成on update set null
@还可以同时做两个外键,如写成foreign key(id,money) references A(id,money) 即可
@两张表关联字段名可以取不一样名字,但类型必须一致
六、查询语句篇
where 条件
group by 字段 having 条件 ／／可用于自动去重分组和计算各分组总数
多表查询、套用表查询
－－－常用语句
- order by 字段名 desc (可选项，加上由大到小，默认由小到大） ／／排序 ,可以和select update where一起使用
- limit n 查看或选择n条记录 limit n,m查看或选择从n条记录开始，到n+m结尾的记录， 可以和select update where一起使用
- like 子句中使用百分号 %字符来表示任意字符 , 如果没有使用百分号 %, LIKE 子句与等号 = 的效果是一样的
- or , and 多条件连接关键字or或 and 且
- union 操作符用于连接两个以上的 SELECT 语句的结果组合到一个结果集合中。多个 SELECT 语句会删除重复的数据。
- inner join（内连接,或等值连接）：获取两个表中字段匹配关系的记录。
- left join（左连接）：获取左表所有记录，即使右表没有对应匹配的记录。
- right join 右连接）： 与 LEFT JOIN 相反，用于获取右表所有记录，即使左表没有对应匹配的记录。
- cross join 交叉
- 正规使用方式：字段名 regexp "正规表达式"
- is null 判断是否为空值
－－－ＥＮＤ
1) order by limint like 使用
MariaDB [lcf]> select * from k8 order by id;
／／ 以k8表字段id从小到大方式排序查看所有记录
MariaDB [lcf]> select * from k8 order by id desc;
／／ 以k8表字段id从大到小方式排序查看所有记录
MariaDB [lcf]> select name,id from k8 limit 2;
／／ 只显示k8表中字段name id的排列的２行记录，也可以写成id,name 看需求。
MariaDB [lcf]> select * from k8 limit 2,4;
／／ 只显示k8表中所有记录中第２行到2+4行的记录，也就是２－６行间的记录。
MariaDB [lcf]> select * from k8 where hobby like "si%"
／／使用方法：和where 共用，％表示模糊匹配任意字符，不加时相当于＝号精确匹配， 字段名 like ".."
2) or ,and union 使用
MariaDB [lcf]> select * from k8 where sex="boy" and age>15;
MariaDB [lcf]> select * from k8 where ( age=15 or age=35) and name="jm" ;
／／这里()的作用是表明整体或优先级匹配
－－－union使用例子
／／主要作用就是将两个或多个表的数据合并在指定字段列上一起显示，不同字段类型也可合并，但字段数必须一致。
MariaDB [lcf]> select id from k8 union select name from k1;
／／union all / distinct 后有两个可选项，都不写时默认是去重复distinct，要显示重复时加上all
MariaDB [lcf]> select id,name from k8 union select id,name from k1;
／／当选用多字段时，会失去去重效果。
3) join 的使用，相当于连接两张表的字段再指定条件显示记录
－－inner join 求交集的记录
MariaDB [lcf]> select * from k8 a inner join k1 b on a.id=b.id;
／／格式：表１ 变量名１inner join 表２ 变量２ on 条件；
／／显示k8表和k1表id号相同（相当于一个条件）的k8表记录
／／以上命令等同于 select * from k8,k1 where k8.id=k1.id; 的查询结果。
MariaDB [lcf]> select k8.id,k1,name from k8 a inner join k1 b on a.id=b.id;
／／可以指定显示的字段列；
－－left join 求交集和左表记录
MariaDB [lcf]> select * from k8 a left join k1 b on a.id=b.id;
／／left join会读取左边数据表的全部数据，即便右边表无对应数据。
－－left join 求交集和右表记录;
MariaDB [lcf]> select * from k8 a right join k1 b on a.id=b.id;
－－cross join
MariaDB [lcf]> select * from k8 a cross join k1 b on a.id=b.id
4) 正规，is noll
MariaDB [lcf]> select * from k8 where name regexp "s";
／／查找字段name值是否包含有a的记录
MariaDB [lcf]> select * from k8 where name regexp "^t.*m$";
／／查找字段name值以t开头m结尾的记录
MariaDB [lcf]> select * from k8 where hobby is not null;
／／查找字段hobby值不为空的记录，如果去掉not则变成查找为空的记录
５) group by ,having 使用
count()求显示总数, sum()求数值总和, avg()求平均值等函数
MariaDB [lcf]> select sex,count(*)how from k8 group by sex;
+------+-----+
| sex | how |
+------+-----+
| boy | 5 |
| girl | 1 |
+------+-----+
2 rows in set (0.00 sec)
／／显示k8表中字段sex值不重复的记录,并用count 计算每组中的个数再用变量how作为列标题显示
MariaDB [lcf]> select sex,count(*)how from k8 group by sex having how>1;
+------+-----+
| sex | how |
+------+-----+
| boy | 5 |
+------+-----+
1 row in set (0.00 sec)
／／同上，having 条件可以和group搭配使用，where不可以和group搭配使用。
mysql> SELECT coalesce(name, '总数'), SUM(singin) as singin_count FROM employee_tbl GROUP BY name with ROLLUP; +--------------------------+--------------+ | coalesce(name, '总数') | singin_count | +--------------------------+--------------+ | 小丽 | 2 | | 小明 | 7 | | 小王 | 7 | | 总数 | 16 | +--------------------------+--------------+ 4 rows in set (0.01 sec)／／
6) 多表查询、套用查询
MariaDB [lcf]> select * from k8,k1 where k8.id=k1.id;
／／显示k8和k1字段id值相等的记录
MariaDB [lcf]> select * from k8 where age > (select avg(age) from k8);
／／显示k8表字段age值大于所有age总各平均值的记录，(...)相当于一个整体，把输出值当变量使用了
mysql> select id,name,pay from us where name in (select 用户名 from users where name regexp "^a.*");
+----+-------+----------+
| id | name | pay |
+----+-------+----------+
| 4 | adm | 5000.00 |
| 35 | avahi | 10000.00 |
+----+-------+----------+
2 rows in set (0.00 sec)
//常用判断符或in()来作比较
六、文件导入导出
1、MariaDB [lcf]> show variables like "secure_file_priv"; ／／查看当前指定的文件导入导出目录
或查看 cat /etc/my.cnf
2、编辑my.ini中对应datadir （仅参考）
改前：datadir="D:\MySQL Data\data\"
改后：datadir="D:\MySQL Data\MySQL Server 5.5\data\"
自定义文件夹后要用chown mysql 新建的目录
３、其中option参数常用的5个选项
FIELDS TERMINATED BY ‘字符串’：设置字符串为字段的分割符，默认值为 \t；
FIELDS ENCLOSED BY ‘字符’：设置字符串括上char varchar text等字符型字段，默认值为 无任何符号；
FIELDS OPTIONALLY ENCLOSED BY ‘字符’：设置字符串括上字段的值，默认值为 无任何符号；
LINES STARTING BY ‘字符串’：设置每一行开头的字符，默认值为 无任何字符；
FIELDS ESCAPED BY ‘字符’：设置转义字符，默认值为 \；
LINES TERMINATED BY ‘字符串’：设置每行结束符，默认值为 \n；
－－导入
MariaDB [lcf]>load data local infile "e:/data.txt"
／／关键字load data [local] 路径，加上local可选项后可以不从默认存放目录中导入文件
->into table water ／／关键字into table 表名，导入到哪个表中
->fields terminated by '：' ／／关键字fields terminated by ‘分割符’ ，指定分割符
->lines terminated by '\n' ／／关键字lines terminated by "换行符“，指结束符，一般用\n换行符
->(id,name); ／／指定要导入到表中的哪些字段，可选项
load data local infile "/data.txt" into table water fields terminated by '：' lines terminated by '\n' (id,name);
／／导入本地目录/data.txt文件到water表中的id，name字段列值里，以：作为分割符，以\n换行作为结尾符
－－导出
MariaDB [lcf]> select * from k8 into outfile "/var/lib/mysql/m3.txt";
MariaDB [lcf]> select id,name from k8 where id>10 into outfile "/var/lib/mysql/m1.txt";
／／关键字 into outfile 路径，可以选择要导出的字段记录和作条件筛选
MariaDB [lcf]> select * from k8 into outfile "/var/lib/mysql/mydd.txt" fields terminated by '：' lines terminated by '\n' ;
／／这里也可以和导入那样指定分割符，和结尾符
－－命令行备份还原数据库（以下命令默认在[root@pc66 mysql]#模式下执行）
使用格式：mysqldump -h填自己真实的主机ＩＰ或域名 -u填数据库账号 -p密码 数据库名 > 备份的文件名.sql
－－－下面我的主机是locahost本机，用户名root，密码１，库名lcf
常规备份
[root@pc66 mysql]# mysqldump -hlocalhost -uroot -p1 lcf > backupfile.sql.gz
－－－
备份MySQL数据库为带删除表的格式，能够让该备份覆盖已有数据库而不需要手动删除原有数据库。
mysqldump -–add-drop-table -uroot -p1 lcf > backupfile.sql
－－－－
直接将MySQL数据库压缩备份
mysqldump -hlocalhost -uroot -p1 lcf | gzip > backupfile.sql.gz
－－－－
备份MySQL数据库某个(些)表mysqldump -hlocalhost -uroot -p1 lcf able1 table2 > backupfile.sql
－－－
同时备份多个MySQL数据库mysqldump -hlocalhost -uroot -p1 –databases lcf1 lcf2 lcf3 > multibackupfile.sql
－－－
仅仅备份数据库结构mysqldump –no-data –databases lcf1 lcf2 lcf3 > structurebackupfile.sql
－－－
备份服务器上所有数据库mysqldump –all-databases > allbackupfile.sql
－－－
还原MySQL数据库的命令mysql -hlocalhost -uroot -p1 lcf < backupfile.sql
－－－
还原压缩的MySQL数据库
gunzip < backupfile.sql.gz | mysql -uroot -p1 lcf
－－－
将数据库转移到新服务器
mysqldump -uroot -p1 lcf | mysql –host=*.*.*.* -C databasename七、其它实用资料方法
１） 设置默认引擎方法
[root@pc66 mysql]# vim /etc/my.cnf
[mysqld] 在这下面添加
default-storage-engine=MyISAM
２）Ｍysql常用配置及优化 （别处摘要https://www.cnblogs.com/zhshto/p/6653424.html）
[client]
# 该目录下的内容常用来进行localhost登陆，一般不需要修改
port = 3306 # 端口号
socket = /var/lib/mysql/mysql.sock # 套接字文件(localhost登陆会自动生成)
[mysql]
# 包含一些客户端mysql命令行的配置
no-auto-rehash # 默认不自动补全 auto-rehash自动补全
[mysqld]
# mysql优化的配置目录，除硬件和环境配置外，全部优化在此配置，
# 一般服务器安装只有此配置目录
user = mysql #默认启动用户，一般不需要修改，可能出现启动不成功
port = 3306 #端口号
socket = /var/lib/mysql/mysql.sock #套接字文件 （套接字方式登陆比TCP/IP方式连接快）
character-set-server = utf8 #设置数据库服务器默认编码 utf-8
basedir = /usr/local/mysql #数据库安装目录--指定此参数可解决相对路径造成的问题
datadir = /data/mysql #数据库目录，数据库目录切换时需要用到
pid-file = /var/run/mysqld/mysqld.pid #mysql进程文件，可指定自己的进程文件
external-locking = FALSE #外部锁定(非多服务器可不设置该选项，默认skip-external-locking)
skip-external-locking #跳过外部锁定 （避免多进程环境下的external locking--锁机制）
skip-name-resolve #跳过主机名解析，直接IP访问，可提升访问速度
log-error = /data/log/mysqld.log #错误日志文件
log-warnings #默认为1,表示启用警告信息记录日志,不需要置0即可,大于1时表示将错误或者失败连接记录日志
open_files_limit = 10240 #全局只读变量,文件描述符限制 注：上限其实为OS文件描述符上限，小于OS上限时生效 可用lsof查看限制并修改相应配置
#以下配置较为重要
back_log = 600 #在MYSQL暂时停止响应新请求之前，短时间内的多少个请求可以被存在堆栈中。不超过TCP/IP监听队列，建议512倍数
#如果系统在短时间内有很多连接，则需要增大该参数的值，该参数值指定到来的TCP/IP连接的监听队列的大小。默认值50。
max_connections = 5000 #MySQL允许最大的进程连接数，如果经常出现Too Many Connections的错误提示，则需要增大此值
max_connect_errors = 6000 #设置每个主机的连接请求异常中断的最大次数，当超过该次数，MYSQL服务器将禁止host的连接请求，
#直到mysql服务器重启或通过flush hosts命令清空此host的相关信息
max_allowed_packet = 32M #限制接收数据包的大小,单条数据超过该值时插入或更新失败,
#show VARIABLES like '%max_allowed_packet%'; 查看当前限制大小
sort_buffer_size = 8M #每个连接独享，用于优化不能通过sql或者索引优化的group和order等，设置的值应适中
#比如：500个连接，500*8 = 4G,将消耗4G内存
join_buffer_size = 8M #用于表间关联缓存的大小,每个连接独享
#thread_concurrency = 16 #应设为CPU核数的2倍.
thread_cache_size = 600 #线程缓存变量，减少连接的创建，一般设置规则
#1G->8;2G->16;3G->32;根据实际情况可适当加大(个人：2^(n+2))
thread_stack = 1024K #设置MYSQL每个线程的堆栈大小，可设置范围为128K至4GB,默认足够大，一般不用修改
query_cache_size = 128M #查询缓存，设置不宜过大，主要是因为缓存越大，设置失效时间越长。如果高并发写，可以禁用该功能
query_cache_limit = 2M #指定单个查询能够使用的缓冲区大小，默认为1M
query_cache_min_res_unit = 4k #大数据查询4k,小数据查询2k,目的是提高缓存命中率
#数据库引擎相关参数
default-storage-engine = InnoDB #默认数据库引擎
innodb_data_file_path = ibdata1:1024M:autoextend #指定数据文件，初始大小，指定扩展大小 注意与数据库中初始文件大小保持一致
#格式：size-allocation[:autoextend[:max-size-allocation]]
innodb_read_io_threads = 16 #read IO线程,根据cpu核心线程数量设置
innodb_write_io_threads = 16 #write IO线程,根据cpu核心线程数量设置
innodb_thread_concurrency = 48 #服务器有几个CPU就设置为几，建议用默认设置，一般为8
innodb_lock_wait_timeout = 120 #事务获取锁的最长等待时间，超时请求失败
innodb_buffer_pool_size = 80G #类似于myisam的key_pool_size 适当增加可提高命中率，专用服务器可设置为70-80%
#innodb_flush_log_at_trx_commit = 2 #默认值1 每次提交日志记录磁盘 2 日志写入系统缓存 0 不提交也记录，不安全，不推荐
innodb_flush_method = O_DIRECT #控制着innodb数据文件及redo log的打开、刷写模式
#fdatasync(默认)，调用fsync()去刷数据文件与redo log的buffer
#O_DSYNC，innodb会使用O_SYNC方式打开和刷写redo log,使用fsync()刷写数据文件
#O_DIRECT，innodb使用O_DIRECT打开数据文件，使用fsync()刷写数据文件跟redo log
#fsync() 作用：数作用是flush时将与fd文件描述符所指文件有关的buffer刷写到磁盘
innodb_log_files_in_group = 2 #以循环方式将日志文件写到多个文件，默认2
innodb_log_file_size = 4G #数据日志文件大小，较大可提升性能，
innodb_log_buffer_size = 512M #日志文件所用的内存大小，以M为单位。缓冲区更大能提高性能，但意外的故障将会丢失数据(开发人员推荐1-8M---不知道为什么)
#innodb_file_per_table = 1 #独立表空间 innodb 默认一个表空间
#
innodb_autoinc_lock_mode = 2 #主要作用于自增列
#0 这个表示tradition 传统 得到语句级别的锁，具有连续性和重复性，但影响并发的插入
#1 这个表示consecutive 连续 根据一次性插入的数量生成连续的值，
#auto_inc锁不需要一直保持到语句的结束，语句得到了相应的值后就可以提前释放锁
#2 这个表示interleaved 交错 没有auto_inc锁，auto_incremant值可能不是连续的
#Percona XtraDB Cluster 5.7. 基于同步复制的多主MySQL集群解决方案，只支持Innodb引擎
#需要安装wsrep打过补丁的版本
#下载地址：https://www.percona.com/downloads/
pxc_strict_mode = ENFORCING
wsrep_cluster_address = gcomm://192.168.66.242,192.168.66.241 #集群中的节点地址，可以使用主机名或IP
wsrep_node_address = 192.168.66.241 #本机节点地址，可以使用主机名或IP
wsrep_provider = /usr/lib64/galera3/libgalera_smm.so
wsrep_sst_method = xtrabackup-v2 #指定SST方式，支持rsync(最快，需要锁表)，
#mysqldump和xtrabackup，从5.5.33-23.7.6起支持xtrabackup-v2
wsrep_slave_threads = 8 #线程数量
wsrep_cluster_name = Cluster #集群名字，必须统一
wsrep_node_name = Node1 #集群中节点名字，必须唯一
wsrep_sst_auth = "sstuser:8jUthGnAUfWEfJ9" #xtrabackup使用的用户名密码，第一台节点启动时创建，详见安装文档
#wsrep_sst_donor='node3,' #一个逗号分割的节点串作为状态转移源，
#比如wsrep_sst_donor=node5,node3, 如果node5可用，用node5,不可用用node3,
#如果node3不可用，最后的逗号表明让提供商自己选择一个最优的。
transaction_isolation = READ-COMMITTED #事务隔离级别
# 1.READ-UNCOMMITTED-读未提交 --脏读(开发生产均不建议)
# 2.READ-COMMITTE-读已提交 --非默认
# 3.REPEATABLE-READ -可重复读 --可能出现幻影行（Innodb 和 Falcon 通过并发解决幻读问题）
# 4.SERIALIZABLE -串行 --最高级别 可能出现大量超时和锁竞争
tmp_table_size = 10G #查询生成的临时表大小超过该值时会在硬盘生成MyISAM表，如果存在大量group by 等语句，可调整大小
max_heap_table_size = 5G #内存表最大空间限制 表在磁盘中，数据在内存中
explicit_defaults_for_timestamp=1 #主要针对TIMESTAMP列，不指定默认会自动加上notnull属性，第一列会自动添加current timestamp
table-definition-cache = 4096 #表定义相关信息缓存 实际存放的是frm与内存的映射关系
table-open-cache = 4096 #打开表缓存 存放已打开的表句柄
#同时设置生效
interactive_timeout = 120 #mysql在关闭一个交互的连接之前所要等待的秒数(客户端连接)
wait_timeout = 864000 #mysql在关闭一个非交互的连接之前所要等待的秒数 默认8小时(应用程序调用),
#根据应用实际情况决定 show processlist; sleep进程多则相应调小
long_query_time = 2 #慢查询超时设置，默认10秒，记录超过查询时间的语句
slow-query-log=1 #注意之前的版本5.6之前的版本为 log-slow-query 是否记录慢查询日志---作为数据库分析
slow-query-log-file=/data/log/query-slow.log #慢查询日志记录文件
#相关二进制文件设置
log_bin #数据库操作二进制记录，数据库备份，复制所需
binlog_format = ROW #ROW(基于行的复制--安全，但是注意并发) STATEMENT(基于sql语句的复制)，MIXED(混合模式复制)
binlog_cache_size = 4M #二进制日志缓存，提高log-bin记录效率
log_bin_trust_function_creators = 1 #主从复制是需要注意，为了保证主从复制完全一致，需要开启此选项，主从默认阻止函数创建
max_binlog_size = 1G #二进制日志文件大小默认1G 要求大于4096 小于1G
expire_logs_days = 7 #清除过期日志
#binlog_do_db #此参数表示只记录指定数据库的二进制日志
#binlog_ignore_db #忽略某数据库记录
key_buffer_size = 2048M #提高查询命中率，最好不要超过4G,可根据缓存命中率适当调整show global status like 'key_read%';
read_buffer_size = 16M #顺序读缓存，每个连接独享
read_rnd_buffer_size = 64M #随机读缓存，每个连接独享
bulk_insert_buffer_size = 1G #批量插入并且只有向非空表添加数据时才使用该缓存,只对myisam表使用。
#主从复制相关--必须开启log-bin
server-id = 19911216 #主从复制必须，并且各服务器具有唯一性
log_slave_updates #配置从服务器的更新是否写入二进制日志，默认是不打开的
replicate-ignore-db = mysql #主从复制默认忽略的数据库，可用","分隔或使用多条记录
#replicate-do-db=qrs,login #主从复制指定数据库,","号隔开或使用多条记录
[mysqldump] #数据库全量备份
quick #强制mysqldump从服务器一次一行地检索表中的行
max_allowed_packet = 32M #可接收数据包大小
[isamchk] #在mysqld服务器不使用的情况下修复表或在崩溃状态下恢复表
key_buffer = 1024M
sort_buff_size =1024M
read_buffer = 16M
write_buffer = 16M
[myisamchk] #在mysqld服务器不使用的情况下修复表或在崩溃状态下恢复表
key_buffer = 1024M
sort_buff_size = 1024M
read_buffer = 16M
write_buffer = 16M
[mysqld_safe] #safe方式启动数据库，相比于mysqld,会在服务启动后继续监控服务状态，死机时重启
open-files-limit = 8192