1. Install
1.1. Windows
版本5.7准备安装包mysql-5.7.29-winx64.zip，创建配置文件，初始化mysql的时候系统会自动创建一个data目录，我们只需创建一个my.ini文件，放置在E:\mysql-5.7.29 目录中。
[client]
port = 3306
[mysqld]
port = 3306
basedir=E:\mysql-5.7.29 # 设置mysql的安装目录
datadir=E:\mysql-5.7.29\data # 设置mysql数据库的数据的存放目录
max_connections=200 # 允许最大连接数
character-set-server=utf8 # 服务端使用的字符集默认为8比特编码的latin1字符集
default-storage-engine=INNODB # 创建新表时将使用的默认存储引擎
[mysql]
default-character-set=utf8 # 设置mysql客户端默认字符集 Command
# 初始化mysql，搜索cmd找到命令提示符，右键以管理员身份运行，进入bin目录，输入以下命令：
mysqld --initialize --user=mysql --console
# 初始化成功后，系统会在mysql目录下创建data目录，并生成初始密码。我们先记录下这个密码
A temporary password is generated for root@localhost: UlFS,YvAv8g*
# 安装服务显示Service successfully installed.表示安装成功。
mysqld --install mysql
# 启动服务通过已经安装过的服务来启动
net start mysql
bin\mysqld.exe --defaults-file=my.ini
# 修改默认密码
mysql -u root -p
grant all privileges on _._ to 'root'@'%' identified by '[email protected]';
flush privileges;
# 注意：Windows 10 上启动服务可能会缺失DLL，安装vcredist_x64.exe1.2. CentOS
1.2.1. 在线安装
# Install client
yum install mysql
# Install server
yum install mysql mysql-server1.2.2. 离线安装
安装包下载，https://dev.mysql.com/downloads/mysql/ ，mysql-5.7.19-1.el6.x86_64.rpm-bundle.tar
https://downloads.mysql.com/archives/get/p/23/file/mysql-5.7.28-1.el7.x86_64.rpm-bundle.tar
# 删除mysql-lib(系统自带的版本过低)
rpm -qa | grep mysql mariadb
yum remove mysql-libs
rpm -e akonadi-mysql-1.9.2-4.el7.x86_64
rpm -e mariadb-server-5.5.52-1.el7.x86_64
rpm -e mariadb-5.5.52-1.el7.x86_64
rpm -e mariadb-libs-5.5.52-1.el7.x86_64 –nodeps 这里可能需要强制删除
# 安装依赖包
yum install libaio
# 解压安装包
tar -xvf mysql-5.7.19-1.el6.x86_64.rpm-bundle.tar
# 安装各个部件
rpm -ivh mysql-community-common-5.7.19-1.el6.x86_64.rpm
rpm -ivh mysql-community-libs-5.7.19-1.el6.x86_64.rpm
rpm -ivh mysql-community-client-5.7.19-1.el6.x86_64.rpm
rpm -ivh mysql-community-server-5.7.19-1.el6.x86_64.rpm
rpm -ivh mysql-community-devel-8.0.4-0.1.rc.el6.x86_64.rpm
rpm -ivh mysql-community-libs-compat-8.0.4-0.1.rc.el6.x86_64.rpm
# Centos 6 启动服务
service mysqld start
# CentOS 7 启动服务
systemctl start mysqld
# 新版初始化的时候默认生成一个密码：通过日志找到这个密码
[root@node1 ~]# more /var/log/mysqld.log
# 临时密码：Nj_VrX;Ml0gj
# 修改密码(在未修改密码前使用数据库会提示修改密码)
[root@node1 ~]# mysql -u root -p #登陆
Enter password: Nj_VrX;Ml0gj
mysql> set password='[email protected]';
Query OK, 0 rows affected (0.00 sec)
# 设置允许从任何地址登陆
mysql> grant all privileges on *.* to 'root'@'%' identified by '[email protected]';
Query OK, 0 rows affected, 1 warning (0.00 sec)
# 刷新权限,使新密码生效
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
# 注意! mysql8 与早期的版本加密方式不一样, 有一些客户端可能不能用, 需要修改默认的加密方式.
default_authentication_plugin=mysql_native_password1.3. 安全关闭服务
# 留意sock文件位置
/opt/mysql/bin/mysqladmin -uroot --socket=/opt/mysql/mysqld.sock -p shutdown
/opt/mysql-5.7.22/bin/mysqladmin -uroot --socket=/opt/mysql-5.7.22/mysqld.sock -p shutdown1.4. 常用脚本
# start.sh
#!/bin/bash
/opt/mysql/bin/mysqld --defaults-file=/opt/mysql/my.cnf &
# stop.sh
#!/bin/bash
/opt/mysql/bin/mysqladmin -uroot --socket=/opt/mysql/mysqld.sock -p shutdown
# mysql.sh
#!/bin/bash
/opt/mysql/bin/mysql -uroot -P 3306 --socket=/opt/mysql/mysqld.sock -p
# 使用crontab 来实现开机自启动.
@reboot su - mysql -s /bin/bash /opt/mysql/start.sh
Debian
sudo apt-get install mysql-server # 服务端
sudo apt-get install mysql-client # 客户端
sudo apt-get install libmysqlclient-dev # 客户端1.5. 配置
[mysql]
default-character-set=utf8
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
symbolic-links=0
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
character-set-server=utf81.6. 字符集
关于字符集常见的问题就是数据库记录中的中文乱码，数据库表结构的comment注释乱码，UTF8MB4 the charset of mysql database. UTF8MB4才是真正的UTF字符集
/* 创建数据库时指定字符集 */
create database if not exists duchaoqun1 default charset utf8 collate utf8_general_ci;
create database duchaoqun1 default character set gbk collate gbk_chinese_ci;
/* 查看字符集 */
show variables like "%colla%";
show variables like '%char%';
/* 修改数据库字符编码 */
ALTER DATABASE aoye CHARACTER SET utf8 COLLATE utf8_unicode_ci;
/* 修改表字段的字符编码 */
alter table PARAMS modify column PARAM_VALUE varchar(4000) character set utf8;
set collation_server=utf8_general_ci;
set collation_connection=utf8_general_ci;1.7. 用户
-- 创建scm用户，从所有地址都可以访问：
CREATE USER 'scm'@'%' IDENTIFIED BY 'Rexen_123_com_cn';
-- 将cm数据库的全部权限开放给scm用户：
GRANT ALL ON cm.* TO 'scm'@'%';
-- 取消授权
REVOKE ALL ON *.* FROM 'scm'@'%';
-- 授权
GRANT ALL PRIVILEGES ON cm.* TO 'scm'@'%';
-- 删除用户
DROP USER 'scm'@'%';
-- 刷新权限
flush privileges;
-- 修改密码 5.6
UPDATE mysql.user SET password=PASSWORD('[email protected]') WHERE user='root';
-- 修改密码 5.7
update mysql.user set authentication_string=password('[email protected]') where user='root';1.8. JDBC
在连接的时候指定字符集可以避免一些字符集问题
jdbc:mysql://localhost:3306/aoye?useUnicode=true&characterEncoding=utf8
jdbc:mysql://localhost:3306/aoye?useUnicode=true&characterEncoding=UTF-8&useSSL=false1.9. mysql.sock
ps -ef|grep mysql
mysql 61892 61777 0 15:09 ? 00:00:04 /usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data --plugin-dir=/usr/local/mysql/lib/plugin --user=mysql --log-error=/usr/local/mysql/data/localhost.localdomain.err --pid-file=/usr/local/mysql/data/localhost.localdomain.pid
root 63784 59870 0 15:44 pts/5 00:00:00 grep --color=auto mysql
# 这个文件里面可以找到mysql.sock信息，一般默认的就是/tmp/mysql.sock，当然也可以使用find命令来查找。
more /usr/local/mysql/data/localhost.localdomain.err1.10. SQL
—显示表细节信息
DESCRIBE tb_name;
DESC aoye.tb_name;
—查看表DDL语句
SHOW CREATE TABLE aoye.tb_name;
—查看库DDL语句
SHOW CREATE DATABASE aoye;
—显示当前实例中所有数据库
SHOW DATABASES;
—切换客户端所使用的数据库
USE aoye;
—显示当前库中的所有的表
SHOW TABLES;
—显示当前正在连接的数据库
SHOW processlist;
id列：一个标识，你要kill一个语句的时候很有用。
user列：显示单前用户，如果不是root，这个命令就只显示你权限范围内的sql语句。
host列：显示这个语句是从哪个ip的哪个端口上发出的。
db列：显示这个进程目前连接的是哪个数据库。
command列：显示当前连接的执行的命令，一般就是休眠（sleep），查询（query），连接（connect）。
time列：此这个状态持续的时间，单位是秒。
state列：显示使用当前连接的sql语句的状态，很重要的列，
state只是语句执行中的某一个状态，一个sql语句，已查询为例，可能需要经过copyingtotmptable，Sortingresult，Sendingdata等状态才可以完成，
info列，显示这个sql语句，因为长度有限，所以长的sql语句就显示不全，但是一个判断问题语句的重要依据。
—查看系统都支持哪些字符集
SHOW CHARACTER SET;
SHOW CHAR SET;
—查看当前数据库状态
SHOW status LIKE “%Uptime%”;
—查看当前数据库配置参数
SHOW variables LIKE “%connect%”;
函数
— 截取字符串，从第一个字符开始截取12个字符
SELECT substring(id,1,12) as id FROM supermarket_sales;
select now(),sysdate(), —获取系统日期，now() 在执行开始时值就得到了， sysdate() 在函数执行时动态得到值
curdate(),current_date(),current_date, —获取当前日期
curtime(),current_time(),current_time, —获取当前时间
utc_date(), utc_time(), utc_timestamp() —我国位于东八时区，所以本地时间 = UTC 时间 + 8 小时
from dual;
—拆分日期
select sysdate(),year(sysdate()),month(sysdate()),day(sysdate()) from DUAL
Copy
2. MySQL 5.6
主流的版本是5.7，8.x，现在很少用到5.6
3. 初始化数据库
/home/mysql56/mysql/scripts/mysql_install_db —user=mysql —basedir=/home/mysql56/mysql —datedir=/home/mysql56/data —defaults-file=/home/mysql56/mysql/my.cnf
4. 启动数据库
/home/mysql56/mysql/bin/mysqld —defaults-file=/home/mysql56/mysql/my.cnf &
5. 修改默认Root密码
/home/mysql56/mysql/bin/mysqladmin -u root password ‘Rexen_123_com_cn’ —socket=/home/mysql56/mysql/mysqld.sock
6. 关闭数据库
/home/mysql56/mysql/bin/mysqladmin -u root —socket=/home/mysql56/mysql/mysqld.sock -p shutdown
7. 本地客户端连接
/home/mysql56/mysql/bin/mysql -u root -p —socket=/home/mysql56/mysql/mysqld.sock
Copy
8. 数据类型
TEXT
一个BLOB或TEXT列，最大长度为65535(2^16-1)个字符。
MEDIUMBLOB,MEDIUMTEXT
一个BLOB或TEXT列，最大长度为16777215(2^24-1)个字符。
LONGBLOB,LONGTEXT
一个BLOB或TEXT列，最大长度为4294967295(2^32-1)个字符。
9. 用户管理
9.1. 创建用户
— 创建用户，设置允许访问的机器，和初始化密码
CREATE USER ‘username’@‘10.230.1.130’ IDENTIFIED BY ‘123456’;
— 把所有库的所有表 的所有权限 授权给user1在所有机器上的访问,设置密码为www.duchaoqun.com
GRANT ALL PRIVILEGES ON . TO ‘user1’@’%’ IDENTIFIED BY ‘www.duchaoqun.com’ WITH GRANT OPTION;
— 把aoye库的所有表 的所有权限 授权给user1在172.17.2.11机器上的访问,设置密码为www.duchaoqun.com
GRANT ALL PRIVILEGES ON aoye.* TO ‘user1’@‘172.17.2.11’ IDENTIFIED BY ‘www.duchaoqun.com’ WITH GRANT OPTION;
— 刷新权限
FLUSH PRIVILEGES;
/*
all PRIVILEGES 表示赋予所有的权限给指定用户，例如：select,insert,update,delete,create,drop 等，具体权限间用“,”半角逗号分隔。
aoye.* 表示上面的权限是针对于哪个表的，aoye指的是数据库，后面的 * 表示对于所有的表
user1 表示需要授权给那个用户,这个用户可以是存在的用户，也可以是不存在的用户。
172.17.2.11 表示允许远程连接的 IP 地址，如果想不限制链接的 IP 则设置为“%”即可。
www.duchaoqun.com 表示密码
*/
— 注：允许所有机器访问，%代表所有的机器，%有的时候不能代表localhost
— 注: 老版本的MySQL,初始root账户的密码为空,因此任何人可以用root账户不用任何密码来连接MySQL服务器,并具有所有权限.
Copy
9.2. 修改密码：普通用户
— 查看用户信息
select host,user,password from mysql.user;
— 删除匿名用户,因为该匿名用户的权限和root用户是一样的.
drop user ”@‘localhost’;
— 使用root登录，使用SET PASSWORD指定密码，使用PASSWORD() 函数来加密
set password for ‘root’@‘localhost’ = password(’******’);
set password for ‘root’@‘127.0.0.1’ = password(’******’);
— 刷新权限
flush privileges;
Copy
9.3. 修改密码：root用户
— 提示输入原密码，输入正确后即可修改，简单修改Root用户的密码
mysqladmin -u root -p password 123456
— 修改密码
UPDATE mysql.user SET password=PASSWORD(’******’) WHERE user=‘root’;
Copy
9.4. 拯救密码
/etc/init.d/mysql stop # 结束当前正在运行的mysql进程。
/usr/bin/mysqld_safe —skip-grant-tables # 用mysql安全模式运行并跳过权限验证。
/usr/bin/mysqld —skip-grant-tables # 同上 用mysql安全模式运行并跳过权限验证。
/usr/bin/mysql -u root # 以root身份登录mysql
mysql> use mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> update user set Password = PASSWORD(‘root’) where User =‘root’; # 修改密码
Query OK, 3 rows affected (0.00 sec)
Rows matched: 3 Changed: 3 Warnings: 0
mysql> exit
/etc/init.d/mysql restart # 结束mysql安全模式，用正常模式运行mysql
Copy
• mysql.user表中Host为%的含义
Host列指定了允许用户登录所使用的IP，比如user=root Host=192.168.1.1。这里的意思就是说root用户只能通过192.168.1.1的客户端去访问。
而%是个通配符，如果Host=192.168.1.%，那么就表示只要是IP地址前缀为“192.168.1.”的客户端都可以连接。如果Host=%，表示所有IP都有连接权限。
这也就是为什么在开启远程连接的时候，大部分人都直接把Host改成%的缘故，为了省事。
10. 命令
10.1. mysqladmin
mysqladmin -uroot [email protected] variables extended-status
11. 参数
11.1. max_connections
—配置的最大连接数
show variables like ‘max_connections’;
—已经使用的最大连接数，应该小于max_used_connections
show global status like ‘max_used_connections’;
set GLOBAL max_connections=192 —设置最大连接数
11.2. tmp_table_size && max_heap_table_size
The table “TMP2321213131XXXX” is full 问题.
设置表的容量(注意单位),过大或者过小都有问题
set GLOBAL tmp_table_size = 67108864;
set GLOBAL max_heap_table_size=32_1024_1024 ;
-
back_log
指定MySQL可能的连接数量。当MySQL主线程在很短的时间内得到非常多的连接请求，该参数就起作用，之后主线程花些时间（尽管很短）检查连接并且启动一个新线程。
back_log参数的值指出在MySQL暂时停止响应新请求之前的短时间内多少个请求可以被存在堆栈中。如果系统在一个短时间内有很多连接，则需要增大该参数的值，该参数值指定到来的TCP/IP连接的侦听队列的大小。不同的操作系统在这个队列大小上有它自己的限制。 试图设定back_log高于你的操作系统的限制将是无效的。
当观察MySQL进程列表，发现大量 264084 | unauthenticated user | xxx.xxx.xxx.xxx | NULL | Connect | NULL | login | NULL 的待连接进程时，就要加大 back_log 的值。back_log默认值为50。2. basedir
MySQL主程序所在路径，即：—basedir参数的值。3. bdb_cache_size
分配给BDB类型数据表的缓存索引和行排列的缓冲区大小，如果不使用DBD类型数据表，则应该在启动MySQL时加载 —skip-bdb 参数以避免内存浪费。 -
bdb_log_buffer_size
分配给BDB类型数据表的缓存索引和行排列的缓冲区大小，如果不使用DBD类型数据表，则应该将该参数值设置为0，或者在启动MySQL时加载 —skip-bdb 参数以避免内存浪费。 -
bdb_home
参见 —bdb-home 选项。4. bdb_max_lock
指定最大的锁表进程数量（默认为10000），如果使用BDB类型数据表，则可以使用该参数。如果在执行大型事物处理或者查询时发现 bdb: Lock table is out of available locks or Got error 12 from … 错误，则应该加大该参数值。5. bdb_logdir
指定使用BDB类型数据表提供服务时的日志存放位置。即为 —bdb-logdir 的值。6. bdb_shared_data
如果使用 —bdb-shared-data 选项则该参数值为On。7. bdb_tmpdir
BDB类型数据表的临时文件目录。即为 —bdb-tmpdir 的值。8. binlog_cache_size
为binary log指定在查询请求处理过程中SQL 查询语句使用的缓存大小。如果频繁应用于大量、复杂的SQL表达式处理，则应该加大该参数值以获得性能提升。9. bulk_insert_buffer_size
指定 MyISAM 类型数据表表使用特殊的树形结构的缓存。使用整块方式(bulk)能够加快插入操作( INSERT … SELECT, INSERT … VALUES (…), (…), …, 和 LOAD DATA INFILE) 的速度和效率。该参数限制每个线程使用的树形结构缓存大小，如果设置为0则禁用该加速缓存功能。注意：该参数对应的缓存操作只能用户向非空数据表中执行插入操作！默认值为 8MB。10. character_set
MySQL的默认字符集。11. character_sets
MySQL所能提供支持的字符集。12. concurrent_inserts
如果开启该参数，MySQL则允许在执行 SELECT 操作的同时进行 INSERT 操作。如果要关闭该参数，可以在启动 mysqld 时加载 —safe 选项，或者使用 —skip-new 选项。默认为On。13. connect_timeout
指定MySQL服务等待应答一个连接报文的最大秒数，超出该时间，MySQL向客户端返回 bad handshake。14. datadir
指定数据库路径。即为 —datadir 选项的值。15. delay_key_write
该参数只对 MyISAM 类型数据表有效。有如下的取值种类：
off: 如果在建表语句中使用 CREATE TABLE … DELAYED_KEY_WRITES，则全部忽略
DELAYED_KEY_WRITES；
on: 如果在建表语句中使用 CREATE TABLE … DELAYED_KEY_WRITES，则使用该选项（默认）；
all: 所有打开的数据表都将按照 DELAYED_KEY_WRITES 处理。
如果 DELAYED_KEY_WRITES 开启，对于已经打开的数据表而言，在每次索引更新时都不刷新带有
DELAYED_KEY_WRITES 选项的数据表的key buffer，除非该数据表关闭。该参数会大幅提升写入键值的速
度。如果使用该参数，则应该检查所有数据表：myisamchk —fast —force。 -
delayed_insert_limit
在插入delayed_insert_limit行后，INSERT DELAYED处理模块将检查是否有未执行的SELECT语句。如果有，在继续处理前执行允许这些语句。16. delayed_insert_timeout
一个INSERT DELAYED线程应该在终止之前等待INSERT语句的时间。17. delayed_queue_size
为处理INSERT DELAYED分配的队列大小（以行为单位）。如果排队满了，任何进行INSERT DELAYED的客户必须等待队列空间释放后才能继续。18. flush
在启动MySQL时加载 —flush 参数打开该功能。19. flush_time
如果该设置为非0值，那么每flush_time秒，所有打开的表将被关，以释放资源和sync到磁盘。注意：只建议在使用 Windows9x/Me 或者当前操作系统资源严重不足时才使用该参数！20. ft_boolean_syntax
搜索引擎维护员希望更改允许用于逻辑全文搜索的操作符。这些则由变量 ft_boolean_syntax 控制。21. ft_min_word_len
指定被索引的关键词的最小长度。注意：在更改该参数值后，索引必须重建！22. ft_max_word_len
指定被索引的关键词的最大长度。注意：在更改该参数值后，索引必须重建！23. ft_max_word_len_for_sort
指定在使用REPAIR, CREATE INDEX, or ALTER TABLE等方法进行快速全文索引重建过程中所能使用的关键词的最大长度。超出该长度限制的关键词将使用低速方式进行插入。加大该参数的值，MySQL将会建立更大的临时文件（这会减轻CPU负载，但效率将取决于磁盘I/O效率），并且在一个排序取内存放更少的键值。24. ft_stopword_file
从 ft_stopword_file 变量指定的文件中读取列表。在修改了 stopword 列表后，必须重建 FULLTEXT 索引。25. have_innodb
YES: MySQL支持InnoDB类型数据表； DISABLE: 使用 —skip-innodb 关闭对InnoDB类型数据表的支持。26. have_bdb
YES: MySQL支持伯克利类型数据表； DISABLE: 使用 —skip-bdb 关闭对伯克利类型数据表的支持。27. have_raid
YES: 使MySQL支持RAID功能。28. have_openssl
YES: 使MySQL支持SSL加密协议。29. init_file
指定一个包含SQL查询语句的文件，该文件在MySQL启动时将被加载，文件中的SQL语句也会被执行。30. interactive_timeout
服务器在关上它前在一个交互连接上等待行动的秒数。一个交互的客户被定义为对mysql_real_connect()使用CLIENT_INTERACTIVE选项的客户。也可见wait_timeout。31. join_buffer_size
用于全部联合(join)的缓冲区大小(不是用索引的联结)。缓冲区对2个表间的每个全部联结分配一次缓冲区，当增加索引不可能时，增加该值可得到一个更快的全部联结。（通常得到快速联结的最佳方法是增加索引。）32. key_buffer_size
用于索引块的缓冲区大小，增加它可得到更好处理的索引(对所有读和多重写)，到你能负担得起那样多。如果你使它太大，系统将开始变慢慢。必须为OS文件系统缓存留下一些空间。为了在写入多个行时得到更多的速度。33. language
用户输出报错信息的语言。34. large_file_support
开启大文件支持。35. locked_in_memory
使用 —memlock 将mysqld锁定在内存中。36. log
记录所有查询操作。37. log_update
开启update log。38. log_bin
开启 binary log。39. log_slave_updates
如果使用链状同步或者多台Slave之间进行同步则需要开启此参数。40. long_query_time
如果一个查询所用时间超过该参数值，则该查询操作将被记录在Slow_queries中。41. lower_case_table_names
1: MySQL总使用小写字母进行SQL操作；
0: 关闭该功能。
注意：如果使用该参数，则应该在启用前将所有数据表转换为小写字母。42. max_allowed_packet
一个查询语句包的最大尺寸。消息缓冲区被初始化为net_buffer_length字节，但是可在需要时增加到max_allowed_packet个字节。该值太小则会在处理大包时产生错误。如果使用大的BLOB列，必须增加该值。43. net_buffer_length
通信缓冲区在查询期间被重置到该大小。通常不要改变该参数值，但是如果内存不足，可以将它设置为查询期望的大小。（即，客户发出的SQL语句期望的长度。如果语句超过这个长度，缓冲区自动地被扩大，直到max_allowed_packet个字节。）44. max_binlog_cache_size
指定binary log缓存的最大容量，如果设置的过小，则在执行复杂查询语句时MySQL会出错。45. max_binlog_size
指定binary log文件的最大容量，默认为1GB。46. max_connections
允许同时连接MySQL服务器的客户数量。如果超出该值，MySQL会返回Too many connections错误，但通常情况下，MySQL能够自行解决。47. max_connect_errors
对于同一主机，如果有超出该参数值个数的中断错误连接，则该主机将被禁止连接。如需对该主机进行解禁，执行：FLUSH HOST;。48. max_delayed_threads
不要启动多于的这个数字的线程来处理INSERT DELAYED语句。如果你试图在所有INSERT DELAYED线程在用后向一张新表插入数据，行将被插入，就像DELAYED属性没被指定那样。49. max_heap_table_size
内存表所能使用的最大容量。50. max_join_size
如果要查询多于max_join_size个记录的联合将返回一个错误。如果要执行没有一个WHERE的语句并且耗费大量时间，且返回上百万行的联结，则需要加大该参数值。51. max_sort_length
在排序BLOB或TEXT值时使用的字节数(每个值仅头max_sort_length个字节被使用；其余的被忽略)。52. max_user_connections
指定来自同一用户的最多连接数。设置为0则代表不限制。53. max_tmp_tables
（该参数目前还没有作用）。一个客户能同时保持打开的临时表的最大数量。54. max_write_lock_count
当出现max_write_lock_count个写入锁定数量后，开始允许一些被锁定的读操作开始执行。避免写入锁定过多，读取操作处于长时间等待状态。55. myisam_recover_options
12. 系统表
打开MySQL的 information_schema 数据库
12.1. TABLES
在该库中有一个 TABLES 表，这个表主要字段分别是：
TABLE_SCHEMA : 数据库名
TABLE_NAME：表名
ENGINE：所使用的存储引擎
TABLES_ROWS：记录数
DATA_LENGTH：数据大小
INDEX_LENGTH：索引大小
• 查看表空间
SELECT TABLE_NAME,DATA_LENGTH+INDEX_LENGTH,TABLE_ROWS FROM TABLES WHERE TABLE_SCHEMA=‘数据库名’ AND TABLE_NAME=‘表名’
Copy
13. 基本语句
写SQL的时候尽量关键字使用大写字母,用户自己的内容使用小写字母.
字段名称使用2个以上单词来描述字段属性.
13.1. 操作数据库
—指定数据库默认字符集,如果不指定默认的字符集
—则系统会根据character_set_database的值进行设置
CREATE DATABASE db_name DEFAULT CHARACTER SET utf8;
CREATE DATABASE saic_metadata /*!40100 DEFAULT CHARACTER SET utf8mb4 */
—删除数据库
DROP DATABASE db_name
Copy
13.2. 数据库重命名
在MySQL中没有为数据库重命名的命令，但我们可用mysqldump转储数据库，再创建一个新的空数据库，把转储文件加载到该新数据库，这样就完成数据库重命名的工作.
% mysqldump old_db >db.sql # 转储db数据库数据
% mysqladmin create new_db # 新建一个空的数据库
% mysql new_db < db.sql # 把db数据库的数据加载到新的数据库中
% mysqladmin drop old_db # 删除旧的数据库一个更简单的重命名数据库的方法是直接修改数据库目录名，但该方法不适用于InnoDB和BDB数据库。注意，在更名后，需在权限表中更新相关数据表信息，需执行以下语句:
mysql> UPDATE db SET db='new_db' WHERE db='old_db';
mysql> UPDATE tables_priv SET db='new_db' WHERE db='old_db';
mysql> UPDATE columns_priv SET db='new_db' WHERE db='old_db';
mysql> UPDATE host SET db='new_db' WHERE db='old_db';13.3. 操作表
—存储引擎使用InnoDB 默认字符集是utf8
—如果没有定义表的默认字符集，则他会按照character_set_database的值来设置
CREATE TABLE
aoye.tb_name
(
id_int INTEGER auto_increment COMMENT ‘主键,默认非空,自增字段’ ,
id_char VARCHAR(20) NOT NULL COMMENT ‘表ID,非空字段,使用cmmment注解’,
name VARCHAR(20) CHARACTER SET utf8 COMMENT ‘常规column,可以指定column字符集’,
DESC varchar(4000) COMMENT ‘使用关键字作为列名,需要添加反引号’,
PRIMARY KEY(id_int)
)
ENGINE=InnoDB DEFAULT CHARSET=utf8;
—存储引擎使用InnoDB 默认字符集是utf8
—表：如果没有定义表的默认字符集，则他会按照character_set_database的值来设置
—添加主键
alter table tb_name add primary key (id);
—添加自增字段，必须是主键
alter table tb_name modify id int auto_increment;
alter table tb_name change regionid region_id VARCHAR(100); —Mysql 修改字段名称
alter table tb_name add(relate int) —修改表结构
Copy
13.4. 操作表索引
—创建索引
—原有一unique索引AK_PAS_Name(PAC_Name)在表tb_webparamcounter中，
alter table tb_webparamcounter drop index AK_PAS_Name; —删除索引
alter table tb_webparamcounter add UNIQUE AK_PAS_Name(PC_ID,PAC_Name); —添加索引
—若发现索引的逻辑不对，还需要再加一个字段进去，执行删除再创建
alter table tb_webparamcounter drop index AK_PAS_Name;
alter table tb_webparamcounter add UNIQUE AK_PAS_Name(PC_ID,PAC_Name,PAC_Value); —创建唯一索引
—注意：这时的PC_ID,PAC_Name,PAC_Value三个字段不是FOREIGN KEY
Copy
13.5. 增删改查
— 新增数据
insert into timenow(id,insert_time) values(1,‘2013-02-22 16:00:00’);
— 查询数据
— limit 控制输出内容
select * from tables limit 10; —显示前10条记录
select * from tables limit 2,4 —从第二个位置开始查询，查询4条记录。
— || 拼接字符串，把’char’中的字符串拼在一起
select first_result||‘ddd’,count(*) counter from tpa_alarm_quit_bts_sum
— SQL中如果说一个指标为Null 那么这个两个指标相加的就可能没有结果,或者为NULL
ifnull(f_ms_pdp_usr_1,0)+ifnull(f_ms_pdp_usr_2,0) ifnull如果为空取0（至少mysql可用）
— 在infromix中可以用“today”取今天0点数据
select first_result||‘ddd’ counter from tpa_alarm_quit_bts_sum where first_result = today
— 在MySQL语句中使用if做条件判断
select *,if(sex=1,“男”,“女”) as “性别” from person;
Copy
14. 操作技巧
14.1. 操作时间
select (current year to second),(interval(2) hour to hour ) from tpa_alarm_quit_bts_sum
where first_result = (current year to hour - interval(2) hour to hour ) ||‘:00:00’
Copy
Current 取当前时间把year格式转换成second秒，
Interval 减去2个小时粒度的间隔，
15. 计划任务(事件调度器)(Event Scheduler)
MySQL5.1.x版本中引入了一项新特性EVENT，顾名思义就是事件、定时任务机制，在指定的时间单元内执行特定的任务，因此今后一些对数据定时性操作不再依赖外部程序，而直接使用数据库本身提供的功能。
要查看当前是否已开启事件调度器，可执行如下SQL：
SHOW VARIABLES LIKE ‘event_scheduler’;
或
SELECT @@event_scheduler;
或
SHOW PROCESSLIST;
若显示：
+-----------------+-------+
| Variable_name | Value |
+-----------------+-------+
| event_scheduler | OFF |
+-----------------+-------+
则可执行
SET GLOBAL event_scheduler = 1;
或
SET GLOBAL event_scheduler = ON;
来开启，也可以直接在启动命令加上“–event_scheduler=1”，例如：
mysqld … —event_scheduler=1
my.ini or my.cnf 中的
[mysqld]
添加 event_scheduler=ON
创建事件(CREATE EVENT)
先来看一下它的语法：
CREATE EVENT [IF NOT EXISTS] event_name
ON SCHEDULE schedule
[ON COMPLETION [NOT] PRESERVE]
[ENABLE | DISABLE]
[COMMENT ‘comment’]
DO sql_statement;
schedule:
AT TIMESTAMP [+ INTERVAL INTERVAL]
| EVERY INTERVAL [STARTS TIMESTAMP] [ENDS TIMESTAMP]
INTERVAL:
quantity {YEAR | QUARTER | MONTH | DAY | HOUR | MINUTE |
WEEK | SECOND | YEAR_MONTH | DAY_HOUR | DAY_MINUTE |
DAY_SECOND | HOUR_MINUTE | HOUR_SECOND | MINUTE_SECOND}
Copy
1)首先来看一个简单的例子来演示每秒插入一条记录到数据表
USE test;
CREATE TABLE aaa (timeline TIMESTAMP);
CREATE EVENT e_test_insert
ON SCHEDULE EVERY 1 SECOND
DO INSERT INTO test.aaa VALUES (CURRENT_TIMESTAMP);
等待3秒钟后，再执行查询成功。
- 5天后清空test表：
CREATE EVENT e_test
ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 5 DAY
DO TRUNCATE TABLE test.aaa;
- 2007年7月20日12点整清空test表：
CREATE EVENT e_test
ON SCHEDULE AT TIMESTAMP ‘2007-07-20 12:00:00’
DO TRUNCATE TABLE test.aaa;
- 每天定时清空test表：
CREATE EVENT e_test
ON SCHEDULE EVERY 1 DAY
DO TRUNCATE TABLE test.aaa;
- 5天后开启每天定时清空test表：
CREATE EVENT e_test
ON SCHEDULE EVERY 1 DAY
STARTS CURRENT_TIMESTAMP + INTERVAL 5 DAY
DO TRUNCATE TABLE test.aaa;
- 每天定时清空test表，5天后停止执行：
CREATE EVENT e_test
ON SCHEDULE EVERY 1 DAY
ENDS CURRENT_TIMESTAMP + INTERVAL 5 DAY
DO TRUNCATE TABLE test.aaa;
- 5天后开启每天定时清空test表，一个月后停止执行：
CREATE EVENT e_test
ON SCHEDULE EVERY 1 DAY
STARTS CURRENT_TIMESTAMP + INTERVAL 5 DAY
ENDS CURRENT_TIMESTAMP + INTERVAL 1 MONTH
DO TRUNCATE TABLE test.aaa;
[ON COMPLETION [NOT] PRESERVE]可以设置这个事件是执行一次还是持久执行，默认为NOT PRESERVE。
- 每天定时清空test表(只执行一次，任务完成后就终止该事件)：
CREATE EVENT e_test
ON SCHEDULE EVERY 1 DAY
ON COMPLETION NOT PRESERVE
DO TRUNCATE TABLE test.aaa;
[ENABLE | DISABLE]可是设置该事件创建后状态是否开启或关闭，默认为ENABLE。
[COMMENT ‘comment’]可以给该事件加上注释。
修改事件(ALTER EVENT)
ALTER EVENT event_name
[ON SCHEDULE schedule]
[RENAME TO new_event_name]
[ON COMPLETION [NOT] PRESERVE]
[COMMENT ‘comment’]
[ENABLE | DISABLE]
[DO sql_statement]
- 临时关闭事件
ALTER EVENT e_test DISABLE;
- 开启事件
ALTER EVENT e_test ENABLE;
- 将每天清空test表改为5天清空一次：
ALTER EVENT e_test
ON SCHEDULE EVERY 5 DAY;
删除事件(DROP EVENT)
语法很简单，如下所示：
DROP EVENT [IF EXISTS] event_name
例如删除前面创建的e_test事件
DROP EVENT e_test;
当然前提是这个事件存在，否则会产生ERROR 1513 (HY000): Unknown event错误，因此最好加上IF EXISTS
DROP EVENT IF EXISTS e_test;
16. 使用 bin-log
使用阿里巴巴的canal
https://www.cnblogs.com/kevingrace/p/5907254.html
https://github.com/alibaba/canal/wiki/QuickStart
https://blog.csdn.net/zjerryj/article/details/77152226
17. 更改数据存放路径
17.1. 操作步骤
停止数据库
修改在my.cnf中的配置的数据路径(datadir)
重启数据库
从环境变量中验证是否已经成功修改
17.2. 修改配置文件
18. 查看当前配置文件,看看datadir指向哪里.
19. 这个配置文件可能是默认的位置(/etc/my.cnf), 也可能是我们自定义的位置(/opt/mysql/my.cnf).
[mysqld]
datadir=/du/2438data/mysql
socket=/du/2438data/mysql/mysql.sock #当服务启动之后需要在这个位置创建一个sock文件，用来未客户端提供连接
user=mysql
# 20. Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
[client]
socket=/du/2438data/mysql/mysql.sock #客户端连接的时候回按照这个路径寻找连接文件，如果这个文件被修改了，那么客户端就不能连接到这个地址上。
[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid19.1. 验证修改效果
[root@cloud du]# mysqladmin -u root -p variables|grep datadir
Enter password:
| datadir | /du/2438data/mysql/ |
[root@cloud du]#
service mysqld status
service mysqld stop
Copy
19.2. 其他注意事项
如果mysql与PHP程序一起使用的话，那么PHP配置文件也需要更改，用来支持Mysql访问。
修改php默认指定的mysql连接口：/etc/php.ini 配置文件，默认下面的配置是空的，它会自动寻找默认的地址
mysql.default_socket = /du/2438data/mysql/mysql.sock
20. 备份和恢复数据
20.1. import data
## **导入数据**
# 导入数据库, 常用source命令. 进入mysql数据库控制台, 如mysql -u root -p
mysql>use 数据库
# 然后使用source命令, 后面参数为脚本文件(如这里用到的.sql)
mysql>source /opt/mysql/database1.sql
20.2. dump data
mysqldump -h 10.0.70.101 -u root -p123456 -A > oldmysql.dump
mysql -u root -pRexen_123_com_cn < oldmysql.dump
flush privileges;