Cacher is the code snippet organizer for pro developers

# 启动 MySQL
sudo etc/inint.d/mysql start
mysql.server start # 或者

# 关闭 MySQL
sudo etc/inint.d/mysql stop
mysql.server stop # 或者

# 查看帮助信息
mysql.server --help

# 登录 MySQL
mysql -uroot -p123456

# 连接远程服务器
mysql -uroot -p123456 -h 192.168.0.211 -P 3306


### 更改密码
mysql> USE mysql;
mysql> UPDATE user SET plugin='mysql_native_password' WHERE User='root';
mysql> FLUSH PRIVILEGES;
mysql> exit;

$ service mysql restart

wget http://mysqltuner.com/mysqltuner.pl
chmod +x mysqltuner.pl
./mysqltuner.pl

SET default_storage_engine = MYISAM;
ALTER TABLE t ENGINE = MYISAM;

# vim /etc/mysql/conf.d/
!includedir /etc/mysql/conf.d/
!includedir /etc/mysql/mysql.conf.d/
[mysqld]
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci
max_connections = 1000
max_heap_table_size = 512M
read_buffer_size = 1024M
read_rnd_buffer_size = 1024M
query_cache_size = 128M
query_cache_limit = 1024M
thread_cache_size = 64
thread_stack = 128K
key_buffer_size = 1024M
max_allowed_packet = 1024M
bind-address = 0.0.0.0
innodb_buffer_pool_size=1024MB

show global status

# 备份数据（远程）
mysqldump -h 10.11.224.3 -u root --password=15619CouldCompute  --max_allowed_packet=2147483649 --default-character-set=utf8mb4 --databases cc > cc

# 恢复数据（远程）
mysql -u root -p123456 --default-character-set=utf8mb4 < cc

# 参数
--local-infile=1                    # allow import inline files
--force                             # ignore all errors
--default-character-set=utf8mb4     # set encoding
--max_allowed_packet=2147483649     # max_allowed_packet
--databases cc                      # assign db

# 错误处理 ERROR 1148 (42000) at line 18: The used command is not allowed with this MySQL version
SET GLOBAL local_infile = 1;

# 查看 DATABASES
SHOW DATABASES;

# 查看 TABLES
SHOW TABLES;

# 查看 schema
SHOW FIELDS FROM table / DESCRIBE table;

# 查看 process
SHOW PROCESSLIST;

# 查看【所有】 process
SHOW FULL PROCESSLIST;

# 查看 index
SHOW INDEX FROM yourtable;

# 查看数据库中所用用户
SELECT DISTINCT CONCAT('User: ''',user,'''@''',host,''';') AS query FROM mysql.user;

# 执行语句100次（用于性能测试）
SELECT BENCHMARK (100, 'SELECT * from t'); 

# Show InnoDB status
SHOW ENGINE INNODB STATUS\G;

# Show server runtime status
SHOW GLOBAL STATUS\G;

# 选取source数据表中的word列
sql='select source.word from source'

# 合并多个数据库
insert into source (words)
select words
from
(    select words from L1
    union
    select words from L2
    union
    select words from L3
    union
    select words from L4
    union
    select words from L5
    union
    select words from L6
    union
    select words from L7
    union
    select words from L8)combinetable
    group by words 
);

character_set_server = utf8mb4
innodb_thread_concurrency = 1000
wait_timeout = 31536000
net_write_timeout = 4294967295
net_retry_count = 4294967295
net_read_timeout = 4294967295 
read_buffer_size = 2147483647
max_allowed_packet = 1063741823