MySQL 常见操作

下载地址：https://dev.mysql.com/downloads/

2、常用服务端配置

[mysqld]
datadir=/data/mysql/data
# 为MySQL客户程序与服务器之间的本地通信指定一个套接字文件(Linux下默认是/var/lib/mysql/mysql.sock文件)
socket=/data/mysql/data/mysql.sock
symbolic-links=0
log-error=/data/mysql/logs/mysqld.log
pid-file=/data/mysql/data/mysqld.pid
lower_case_table_names=0
federated
init_connect='SET NAMES utf8'
character_set_server=utf8
transaction-isolation=READ COMMITTED
# key_buffer是用于索引块的缓冲区大小，增加它可得到更好处理的索引(对所有读和多重写)。 索引块是缓冲的并且被所有的线程共享，key_buffer的大小视内存大小而定。
key_buffer=384M      
# 为所有线程打开表的数量。增加该值能增加mysqld要求的文件描述符的数量。可以避免频繁的打开数据表产生的开销                              
table_cache=512
# 每个需要进行排序的线程分配该大小的一个缓冲区。增加这值加速ORDER BY或GROUP BY操作。注意：该参数对应的分配内存是每连接独占！如果有100个连接，那么实际分配的总共排序缓冲区大小为100×6=600MB
sort_buffer_size=2M        
# 读查询操作所能使用的缓冲区大小。和sort_buffer_size一样，该参数对应的分配内存也是每连接独享。                               
read_buffer_size=2M 
# 指定MySQL查询结果缓冲区的大小
query_cache_size=32M
# 在使用行指针排序之后，随机读用的。
read_rnd_buffer_size=8M 
# MyISAM表发生变化时重新排序所需的缓冲
myisam_sort_buffer_size=64M 
# 最大并发线程数，取值为服务器逻辑CPU数量×2，如果CPU支持H.T超线程，再×2
thread_concurrency=8 
#缓存可重用的线程数
thread_cache=8
# 避免MySQL的外部锁定，减少出错几率增强稳定性。 
skip-locking
# back_log参数的值指出在MySQL暂时停止响应新请求之前的短时间内多少个请求可以被存在堆栈中。如果系统在一个短时间内有很多连接，则需要增大该参数的值，该参数值指定到来的TCP/IP连接的侦听队列的大小。试图设定back_log高于你的操作系统的限制将是无效的。默认值为50。对于Linux系统推荐设置为小于512的整数。
back_log=384 
# MySQL服务器同时处理的数据库连接的最大数量(默认设置是100)。超过限制后会报 Too many connections 错误
max_connections=n 
# 用来存放索引区块的RMA值(默认设置是8M)，增加它可得到更好处理的索引(对所有读和多重写)    
key_buffer_size=n
#每个进行一个顺序扫描的线程为其扫描的每张表分配这个大小的一个缓冲区。如果你做很多顺序扫描，你可能想要增加该值。默认数值是131072(128K)    
record_buffer=131072
# 服务器在关闭它之前在一个连接上等待行动的秒数。   
wait_timeout=3
# 服务器在关闭它前在一个交互连接上等待行动的秒数。一个交互的客户被定义为对 mysql_real_connect()使用 CLIENT_INTERACTIVE 选项的客户。默认数值是28800，可以把它改为3600。 
interactive_timeout=3600
# 禁止MySQL对外部连接进行DNS解析，使用这一选项可以消除MySQL进行DNS解析的时间。但需要注意，如果开启该选项，则所有远程主机连接授权都要使用IP地址方式，否则MySQL将无法正常处理连接请求！    
skip-name-resolve            
skip-innodb
# 关闭不需要的表类型
skip-bdb 
# 开启Binlog
log-bin=/data/mysql/data/mysql-bin.log
expire-logs-days=14
max-binlog-size=500M
server-id=1
binlog_format=ROW
binlog_row_image=FULL
# 开启慢查询日志
slow_query_log=1
slow_query_log_file=/data/mysql/logs/slowquery.log
long_query_time=2

[mysqldump]
# 服务器和客户端之间最大能发送的可能信息包 
max_allowed_packet=16M

[mysql]
socket=/data/mysql/data/mysql.sock
default-character-set=utf8

[client]
default-character-set=utf8
socket=/data/mysql/data/mysql.sock

3、初始化数据目录

mkdir -p /data/mysql/{logs,data} &&\
chown -R mysql:mysql /data/mysql &&\
mysqld --initialize --user=mysql

4、MySQL进程服务管理

systemctl enable mysqld
systemctl daemon-reload
systemctl start mysqld
systemctl status mysqld

5、修改root用户本地连接密码

ALTER USER 'root'@'localhost' IDENTIFIED BY '***';
flush privileges;

或者

set password for 'root'@'localhost'=password('***');
flush privileges;

6、添加远程登录用户

默认只允许root帐户在本地登录，如果要在其它机器上连接mysql，必须修改root允许远程连接，或者添加一个允许远程连接的帐户，为了安全起见，添加一个新的帐户：

GRANT ALL PRIVILEGES ON *.* TO 'sonarqube'@'%' IDENTIFIED BY '***' WITH GRANT OPTION;
flush privileges;

7、修改用户密码

①用set password命令

SET PASSWORD FOR 'root'@'localhost' = PASSWORD('***');
flush privileges;

②用mysqladmin

mysqladmin -uroot -p*** password 1234abcd  

格式：mysqladmin -u用户名 -p旧密码 password 新密码

③update更新user表

use mysql 
update user set PASSWORD = PASSWORD('1234abcd') where user = 'root';
flush privileges;

8、跳过密码验证重置root密码

修改my.cnf，追加skip-grant-tables，重启mysql服务，然后就可以不输入密码直接登录

9、表复制

复制表结构及数据到新表

CREATE TABLE 新表 SELECT * FROM 旧表

只复制表结构到新表

CREATE TABLE 新表 SELECT * FROM 旧表 WHERE 1=2
 
即:让WHERE条件不成立.

方法二:(低版本的mysql不支持，mysql4.0.25 不支持，mysql5已经支持了)

CREATE TABLE 新表 LIKE 旧表

复制旧表的数据到新表(假设两个表结构一样)

INSERT INTO 新表 SELECT * FROM 旧表

复制旧表的数据到新表

(假设两个表结构不一样)

INSERT INTO 新表(字段1,字段2,…….) SELECT 字段1,字段2,…… FROM 旧表

参考: https://www.cnblogs.com/lxboy2009/p/7234535.html

10、show status命令

show status like '%下面变量%';

Aborted_clients 							由于客户没有正确关闭连接已经死掉，已经放弃的连接数量。 
Aborted_connects 							尝试已经失败的MySQL服务器的连接的次数。 
Connections 									试图连接MySQL服务器的次数。 
Created_tmp_tables 						当执行语句时，已经被创造了的隐含临时表的数量。 
Delayed_insert_threads 				正在使用的延迟插入处理器线程的数量。 
Delayed_writes								用INSERT DELAYED写入的行数。 
Delayed_errors 								用INSERT DELAYED写入的发生某些错误(可能重复键值)的行数。 
Flush_commands 								执行FLUSH命令的次数。 
Handler_delete 								请求从一张表中删除行的次数。 
Handler_read_first 						请求读入表中第一行的次数。 
Handler_read_key 							请求数字基于键读行。 
Handler_read_next 						请求读入基于一个键的一行的次数。 
Handler_read_rnd 							请求读入基于一个固定位置的一行的次数。 
Handler_update 								请求更新表中一行的次数。 
Handler_write 								请求向表中插入一行的次数。 
Key_blocks_used 							用于关键字缓存的块的数量。 
Key_read_requests 						请求从缓存读入一个键值的次数。 
Key_reads 										从磁盘物理读入一个键值的次数。 
Key_write_requests 						请求将一个关键字块写入缓存次数。 
Key_writes 										将一个键值块物理写入磁盘的次数。 
Max_used_connections 					同时使用的连接的最大数目。 
Not_flushed_key_blocks 				在键缓存中已经改变但是还没被清空到磁盘上的键块。 
Not_flushed_delayed_rows 			在INSERT DELAY队列中等待写入的行的数量。 
Open_tables 									打开表的数量。 
Open_files 										打开文件的数量。 
Open_streams 									打开流的数量(主要用于日志记载） 
Opened_tables 								已经打开的表的数量。 
Questions 										发往服务器的查询的数量。 
Slow_queries 									要花超过long_query_time时间的查询数量。 
Threads_connected 						当前打开的连接的数量。 
Threads_running 							不在睡眠的线程数量。 
Uptime 												服务器工作了多少秒。

11、查看连接数和状态

如果是root帐号，你能看到所有用户的当前连接。如果是其它普通帐号，只能看到自己占用的连接。 
show processlist;只列出前100条，如果想全列出请使用show full processlist; 
mysql> show processlist;

12、查看支持的引擎

show engines；

13、清空表中数据

# truncate语句直接清空表中数据
truncate 表名；
# drop语句清空表中数据
delete from 表名;

14、导入导出数据到CSV

导出

select * from Test.User into 
	outfile 'UserData.csv'
	fields terminated by '@'           # 字段间以@分割
	optionally enclosed by "" 
	escaped by "Curiouser"				
  lines terminated by '\n';         # 数据行之间以\n分割

如果在导出期间出现以下错误

The MySQL server is running with the --secure-file-priv option so it cannot execute this statement

可以用sql>show variables like '%secure%'查看secure-file-priv当前的值。导出的数据文件必须是这个值的指定路径才可以。默认有可能是NULL就代表禁止导出。可在配置文件中设置此变量的值,然后重启服务。

[mysqld]

secure-file-priv=/home/Curiouser/Desktop

导入

sql>load data infile '/var/lib/mysql-files/UserData.csv' 
			into table User
			fields terminated by '@'
			optionally enclosed by ""
			escaped by ""
			lines terminated by '\n';

当CSV文件中的每一行记录的列数小于数据库表时，使用下列语句：

sql>load data infile '/var/lib/mysql-files/UserData.csv' 
 			into table User
 			fields terminated by '@'
 			lines terminated by '\n'
 			(Id,Name,Addr,Phone);

15、MySQL字符集的设置

设置MySQL默认字符集

基于session会话

set character_set_client=utf8mb4;
# 主要用来设置客户端使用的字符集。
set character_set_database=utf8mb4;
# 主要用来设置默认创建数据库的编码格式，如果在创建数据库时没有设置编码格式，就按照这个格式设置。
set character_set_server=utf8mb4;
# 服务器安装时指定的默认编码格式，这个变量建议由系统自己管理，不要人为定义
set character_set_connection=utf8mb4;
# 主要用来设置连接数据库时的字符集，如果程序中没有指明连接数据库使用的字符集类型则按照这个字符集设置。
set character_set_results=utf8mb4;
# 数据库给客户端返回时使用的编码格式，如果没有指明，使用服务器默认的编码格式。
set character_set_system=utf8mb4;
# 数据库系统使用的编码格式，这个值一直是utf8，不需要设置，它是为存储系统元数据的编码格式。
set collation_connection=utf8mb4;
set collation_server=utf8mb4;
set collation_database=utf8mb4;

基于全局global

# 设置全局的数据库字符编码
set global character_set_database=utf8mb4;
set global character_ser_server=utf8mb4;

永久性改变,在配置文件中修改数据库的字符编码(需重启服务)

[mysqld]
character-set-server=utf8mb4 
[client]
default-character-set=utf8mb4 
[mysql]
default-character-set=utf8mb4

字符集的查看

查看MySQL支持的字符集

show charset;

+----------+---------------------------------+---------------------+--------+
| Charset  | Description                     | Default collation   | Maxlen |
+----------+---------------------------------+---------------------+--------+
| big5     | Big5 Traditional Chinese        | big5_chinese_ci     |      2 |
| dec8     | DEC West European               | dec8_swedish_ci     |      1 |
| cp850    | DOS West European               | cp850_general_ci    |      1 |
| hp8      | HP West European                | hp8_english_ci      |      1 |
| koi8r    | KOI8-R Relcom Russian           | koi8r_general_ci    |      1 |
| latin1   | cp1252 West European            | latin1_swedish_ci   |      1 |
| latin2   | ISO 8859-2 Central European     | latin2_general_ci   |      1 |
| swe7     | 7bit Swedish                    | swe7_swedish_ci     |      1 |
| ascii    | US ASCII                        | ascii_general_ci    |      1 |
| ujis     | EUC-JP Japanese                 | ujis_japanese_ci    |      3 |
| sjis     | Shift-JIS Japanese              | sjis_japanese_ci    |      2 |
| hebrew   | ISO 8859-8 Hebrew               | hebrew_general_ci   |      1 |
| tis620   | TIS620 Thai                     | tis620_thai_ci      |      1 |
| euckr    | EUC-KR Korean                   | euckr_korean_ci     |      2 |
| koi8u    | KOI8-U Ukrainian                | koi8u_general_ci    |      1 |
| gb2312   | GB2312 Simplified Chinese       | gb2312_chinese_ci   |      2 |
| greek    | ISO 8859-7 Greek                | greek_general_ci    |      1 |
| cp1250   | Windows Central European        | cp1250_general_ci   |      1 |
| gbk      | GBK Simplified Chinese          | gbk_chinese_ci      |      2 |
| latin5   | ISO 8859-9 Turkish              | latin5_turkish_ci   |      1 |
| armscii8 | ARMSCII-8 Armenian              | armscii8_general_ci |      1 |
| utf8     | UTF-8 Unicode                   | utf8_general_ci     |      3 |
| ucs2     | UCS-2 Unicode                   | ucs2_general_ci     |      2 |
| cp866    | DOS Russian                     | cp866_general_ci    |      1 |
| keybcs2  | DOS Kamenicky Czech-Slovak      | keybcs2_general_ci  |      1 |
| macce    | Mac Central European            | macce_general_ci    |      1 |
| macroman | Mac West European               | macroman_general_ci |      1 |
| cp852    | DOS Central European            | cp852_general_ci    |      1 |
| latin7   | ISO 8859-13 Baltic              | latin7_general_ci   |      1 |
| utf8mb4  | UTF-8 Unicode                   | utf8mb4_general_ci  |      4 |
| cp1251   | Windows Cyrillic                | cp1251_general_ci   |      1 |
| utf16    | UTF-16 Unicode                  | utf16_general_ci    |      4 |
| utf16le  | UTF-16LE Unicode                | utf16le_general_ci  |      4 |
| cp1256   | Windows Arabic                  | cp1256_general_ci   |      1 |
| cp1257   | Windows Baltic                  | cp1257_general_ci   |      1 |
| utf32    | UTF-32 Unicode                  | utf32_general_ci    |      4 |
| binary   | Binary pseudo charset           | binary              |      1 |
| geostd8  | GEOSTD8 Georgian                | geostd8_general_ci  |      1 |
| cp932    | SJIS for Windows Japanese       | cp932_japanese_ci   |      2 |
| eucjpms  | UJIS for Windows Japanese       | eucjpms_japanese_ci |      3 |
| gb18030  | China National Standard GB18030 | gb18030_chinese_ci  |      4 |
+----------+---------------------------------+---------------------+--------+

查看MySQL已配置的默认字符设置

show variables like '%character%';

+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | utf8mb4                    |
| character_set_connection | utf8mb4                    |
| character_set_database   | latin1                     |
| character_set_filesystem | binary                     |
| character_set_results    | utf8mb4                    |
| character_set_server     | latin1                     |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+

查看库已配置的字符设置

show create database test1;
+----------+----------------------------------------------------------------------+
| Database | Create Database                                                      |
+----------+----------------------------------------------------------------------+
| test1    | CREATE DATABASE `test1` /*!40100 DEFAULT CHARACTER SET utf8mb4 */    |
+----------+----------------------------------------------------------------------+

查看表已配置的字符设置

show table status from 库名 like '表名';

查看表中字段已配置的字符设置
```
show full columns from 表名;
```

查看库中所有表的字符集设置

select TABLE_NAME,TABLE_COLLATION from information_schema.`TABLES`;

查看所有库所有表中的字段的字符集设置

select TABLE_SCHEMA ,TABLE_NAME,COLUMN_NAME,COLLATION_NAME from information_schema.`COLUMNS`

修改字符集

修改库的字符集

alter database 库名 default character set 字符集;

修改表的字符集

alter table 表名 convert to character set 字符集;

修改字段的字符集

alter table 表名 modify 字段名 字段属性 character set 字符集；

16、Mysql大小写敏感设置

MySQL在Windows下都不区分大小写。 Linux下MySQL安装完后是默认区分表名的大小写，但不区分列名的大小写。数据库名、表名、列名、别名大小写规则是这样的：

数据库名与表名是严格区分大小写
表的别名是严格区分大小写
列名与列的别名在所有的情况下均是忽略大小写
变量名也是严格区分大小写

修改大小写敏感设置

在/etc/my.cnf 中的[mysqld]后添加添加lower_case_table_names=1（0:区分大小写，1:不区分大小写)，重启MYSQL服务
- 为0时 表示区分大小写，使用CREATE TABLE或CREATE DATABASE语句指定的大小写字母在硬盘上保存表名和数据库名。名称比较对大小写敏感。在大小写不敏感的操作系统如windows或Mac OS x上我们不能将该参数设为0，如果在大小写不敏感的文件系统上将--lowercase-table-names强制设为0，并且使用不同的大小写访问MyISAM表名，可能会导致索引破坏。
- 为1时 表示将名字转化为小写后存储，名称比较对大小写不敏感。MySQL将所有表名转换为小写在存储和查找表上。该行为也适合数据库名和表的别名。该值为Windows的默认值。
- 为2时 表名和数据库名在硬盘上使用CREATE TABLE或CREATE DATABASE语句指定的大小写字母进行保存，但MySQL将它们转换为小写在查找表上。名称比较对大小写不敏感，即按照大小写来保存，按照小写来比较。注释：只在对大小写不敏感的文件系统上使用! innodb表名用小写保存。如果你使用innodb表，为了避免避免大小写敏感问题，可以把lower_case_table_names=1把lower_case_table_names从0改变为1在你把lower_case_table_names设置为1时，在restart你的mysqld之前，请把数据库名和表名更改为小写

如果想在查询时区分字段值的大小写，则字段值需要设置BINARY属性

创建时设置

CREATE TABLE T( 
	A VARCHAR(10) BINARY 
);

使用alter修改

ALTER TABLE `tablename` MODIFY COLUMN `cloname` VARCHAR(45) BINARY;

17、修改表或字段的字符集

修改表的字符集
修改表中字段的字符集

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

mysql-common-operations.md

mysql-common-operations.md

MySQL 常见操作

1、相关网站

2、常用服务端配置

3、初始化数据目录

4、MySQL进程服务管理

5、修改root用户本地连接密码

6、添加远程登录用户

7、修改用户密码

①用set password命令

②用mysqladmin

③update更新user表

8、跳过密码验证重置root密码

9、表复制

复制表结构及数据到新表

只复制表结构到新表

复制旧表的数据到新表(假设两个表结构一样)

复制旧表的数据到新表

10、show status命令

11、查看连接数和状态

12、查看支持的引擎

13、清空表中数据

14、导入导出数据到CSV

导出

导入

15、MySQL字符集的设置

设置MySQL默认字符集

字符集的查看

修改字符集

16、Mysql大小写敏感设置

17、修改表或字段的字符集

Files

mysql-common-operations.md

Latest commit

History

mysql-common-operations.md

File metadata and controls

MySQL 常见操作

1、相关网站

2、常用服务端配置

3、初始化数据目录

4、MySQL进程服务管理

5、修改root用户本地连接密码

6、添加远程登录用户

7、修改用户密码

①用set password命令

②用mysqladmin

③update更新user表

8、跳过密码验证重置root密码

9、表复制

复制表结构及数据到新表

只复制表结构到新表

复制旧表的数据到新表(假设两个表结构一样)

复制旧表的数据到新表

10、show status命令

11、查看连接数和状态

12、查看支持的引擎

13、清空表中数据

14、导入导出数据到CSV

导出

导入

15、MySQL字符集的设置

设置MySQL默认字符集

字符集的查看

修改字符集

16、Mysql大小写敏感设置

17、修改表或字段的字符集