参考：
http://kerry.blog.51cto.com/172631/146259/
http://blog.csdn.net/xin_yu_xin/article/details/7574662
mysql的常用操作
1、登录
mysql -uroot -pdaixuan
2、查看数据库
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| discuz |
| mysql |
| test |
+--------------------+
3、切换数据库
mysql>use test
4、查看当前数据库
mysql>select database();
5、查看当前的用户
mysql>select user();
6、查看当前的数据库版本
mysql> select version();
| 5.1.73-log |
7、查看所有的表、查看一张表的详细信息
mysql>show tables;
mysql> show create table pre_ucenter_post\G;
8、创建数据库
mysql>create database daixuan;
mysql>use daixuan
9、在数据库daixuan中创建表tb1，指定两个字段int型和char型，指定字符集gbk,
mysql> create table tb1 (`id` int(4),`name` char(40)) ENGINE=MyISAM DEFAULT CHARSET=gbk;
mysql>show tables; 查看表
mysql> show create table tb1\G; 查看表的创建信息
*************************** 1. row ***************************
Table: tb1
Create Table: CREATE TABLE `tb1` (
`id` int(4) DEFAULT NULL,
`name` char(40) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=gbk
1 row in set (0.00 sec)
10、向表tb1中插入数据
mysql> insert into tb1 values(1, 'daixuan');
mysql> insert into tb1 values(2, 'aming');
mysql> insert into tb1 (`id`) values(3); id使用的是反斜杠
mysql> insert into tb1 (`name`) values('linux'); name使用的是反斜杠
11、查看表tb1
mysql> select * from tb1;
+------+---------+
| id | name |
+------+---------+
| 1 | daixuan |
| 2 | aming |
| 3 | NULL |
| 4 | linux |
+------+---------+
4 rows in set (0.00 sec)
12、清空表tb1
mysql> truncate table daixuan.tb1;
13、删除表tb1
mysql> drop table tb1;
14、删除数据库daixuan
mysql> drop database daixuan;
15、赋予discuz数据库从192.168.101.网段内的任何IP以daixuan用户登录
mysql> grant all on discuz.* to 'daixuan'@192.168.101.%' identified by 'password'
16、更新数据库
mysql>flush privileges;
17、显示数据库队列
mysql>show processlist;
18、查看变量
mysql>show variables;
19、在mysql内设置修改参数并查看，编辑my.cnf保存才能永久生效
mysql>set global max_connectionns=200;
mysql>show variables like 'max_connec%';
20、查看状态(调优时候使用)
mysql>show status
mysql>show status like '%running%' 通配running
21、查看mysql的错误日志在datadir
[root@daixuan ~]# vim /etc/init.d/mysqld
datadir=/data/mysql
[root@daixuan ~]# cd /data/mysql
[root@daixuan mysql]# ls
daixuan.err
[root@daixuan mysql]# tail daixuan.err 查看错误日志
22、如何修复表
mysql>repair table discuz.pre_forum_post;
23、mysql数据库备份
[root@daixuan ~]# mysqldump -uroot -pdaixuan discuz
[root@daixuan ~]# mysqldump -uroot -pdaixuan discuz > /data/discuz.sql
[root@daixuan ~]# vim !$
vim /data/discuz.sql
24、mysql数据库恢复
如果不小心删除了discuz的某一个表
[root@daixuan discuz]# cd /data/mysql/discuz/
[root@daixuan discuz]# rm -rf pre_forum_post*
[root@daixuan discuz]# /etc/init.d/mysqld restart
Shutting down MySQL.... SUCCESS!
Starting MySQL.. SUCCESS!
这个时候网站已经不能正常打开了
怎么恢复呢？
[root@daixuan discuz]# mysql -uroot -pdaixuan discuz < /data/discuz.sql
25、只备份mysql数据库中的表
[root@daixuan discuz]# mysqldump -uroot -pdaixuan discuz pre_forum_post > /data/discuz.post.sql
[root@daixuan discuz]# vim !$
vim /data/discuz.post.sql
26、恢复mysql数据库中的表，注：恢复只加数据库名，不加表名
[root@daixuan discuz]# mysql -uroot -pdaixuan discuz < /data/discuz.post.sql
27、备份和还原数据库指定字符集
[root@daixuan discuz]# mysqldump -uroot -pdaixuan --default-character-set=gbk discuz pre_forum_post > /data/discuz.post.sql
[root@daixuan discuz]# mysql -uroot -pdaixuan --default-character-set=gbk discuz < /data/discuz.post.sql
28、导入本地sql文件，--local-infile=1
[root@ZAMPDMP-02 ~]# mysql -uroot -p --local-infile=1
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 5624
Server version: 5.6.30-log Source distribution
Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>source /tmp/dim/dim_location_david.sql;
29、将一个数据库的表导入到另一个数据库
mysql> show create table dim_location;
+--------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+--------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| dim_location | CREATE TABLE `dim_location` (
`geo_code` int(10) unsigned NOT NULL DEFAULT '0' COMMENT 'GEO编码',
`province` varchar(100) DEFAULT NULL COMMENT '省份',
`city` varchar(100) DEFAULT NULL COMMENT '城市',
`level` smallint(4) unsigned DEFAULT NULL COMMENT '级别',
`rank` int(8) unsigned DEFAULT NULL COMMENT '顺序',
PRIMARY KEY (`geo_code`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='地域信息' |
+--------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> use exp
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> CREATE TABLE `dim_location` (
-> `geo_code` int(10) unsigned NOT NULL DEFAULT '0' COMMENT 'GEO编码',
-> `province` varchar(100) DEFAULT NULL COMMENT '省份',
-> `city` varchar(100) DEFAULT NULL COMMENT '城市',
-> `level` smallint(4) unsigned DEFAULT NULL COMMENT '级别',
-> `rank` int(8) unsigned DEFAULT NULL COMMENT '顺序',
-> PRIMARY KEY (`geo_code`)
-> ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='地域信息'
-> ;
Query OK, 0 rows affected (0.07 sec)
mysql> use exp;
Database changed
mysql> show tables;
+-------------------------+
| Tables_in_exp |
+-------------------------+
| dim_location |
| export_segment_day |
| location_analysis_day |
| location_analysis_hour |
| promotion_analysis_day |
| promotion_analysis_hour |
| segment_status |
| segment_total |
| segment_trait |
| source_analysis_day |
| source_analysis_hour |
| visit_trend_day |
| visit_trend_hour |
+-------------------------+
13 rows in set (0.00 sec)
mysql> insert into exp.dim_location select * from pub.dim_location;
Query OK, 371 rows affected (0.00 sec)
Records: 371 Duplicates: 0 Warnings: 0
mysql> select * from dim_location;
+------------+--------------------------+-----------------------------------+-------+------+
| geo_code | province | city | level | rank |
+------------+--------------------------+-----------------------------------+-------+------+
| 0 | 未知 | 未知 | 0 | 999 |
| 1000000000 | 其他 | 其他 | 0 | 998 |
| 1156000000 | 其他 | 其他 | 0 | 998 |
| 1156110000 | 北京市 | 北京市 | 1 | 1 |
今天发现general_log竟然有100多个G了，把服务器给搞挂了，我操，太恐怖
general_log是记录用户执行所有的sql记录，瞬间增加这么快。
[root@DMP-GATEWAY data]# du -sh DMP-GATEWAY.log
107MDMP-GATEWAY.log
[root@DMP-GATEWAY data]# du -sh DMP-GATEWAY.log
148MDMP-GATEWAY.log
[root@DMP-GATEWAY data]# du -sh DMP-GATEWAY.log
277MDMP-GATEWAY.log
[root@DMP-GATEWAY data]# du -sh DMP-GATEWAY.log
默认mysql是关闭general_log，如果没有关闭，这样去关闭
mysql -uroot -p
mysql> show variables like "gen%";
+------------------+--------------------------------------+
| Variable_name | Value |
+------------------+--------------------------------------+
| general_log | OFF |
| general_log_file | /opt/amos/mysql/data/DMP-GATEWAY.log |
+------------------+--------------------------------------+
2 rows in set (0.00 sec)
mysql> set global general_log = off;
Query OK, 0 rows affected (0.02 sec)
29.mysql乱码问题
新建的mysql database select ＊ from table正常
但是 set names utf8，之后再select ＊ from table 乱码，web页面也乱码。
解决办法：
mysql -u root -p --default-character-set=utf8
source ／tmp／mm.sql
ok
30、备份和还原实例
mysqldump -uroot -p -h127.0.0.1 --databases som > /opt/amos/mysqlbackup/`date +%Y%m%d`.som.sql
mysql -uroot -p -D som < /opt/amos/mysqlbackup/alldatabase.sql
31、mysql查看数据库中不同表的引擎室InnoDB还是MyISAM
mysql> SELECT table_name FROM information_schema.tables where engine='MyISAM' and table_schema='som';
+---------------------+
| table_name |
+---------------------+
| broker |
| broker_metric |
| cpu |
| cpu_total |
| diskspace |
| global_config |
| instance |
| iostat |
| ip |
| ip_host |
| loadavg |
| memory |
| module_config |
| module_dependency |
| network |
| package |
| service_module |
| service_task_result |
| sl_hc_metric |
| systemStatus |
| vmstat |
| zk_metric |
| zookeeper |
+---------------------+
23 rows in set (0.00 sec)
mysql> SELECT table_name FROM information_schema.tables where engine='InnoDB' and table_schema='kup';
+---------------------------+
| table_name |
+---------------------------+
| app_type |
| auth_assignment |
| auth_item |
| auth_item_child |
| auth_rule |
32、windows登录mysql命令行并导出表
C:\ProgramData\MySQL\MySQL Server 5.7
my.ini
# Secure File Priv.
secure-file-priv="C:/ProgramData/MySQL/MySQL Server 5.7/Uploads"
mysql > select * from child_info into outfile "C:/ProgramData/MySQL/MySQL Server 5.7/Uploads/child_info20170213.txt"
Alter修改表，举例增加一个字段
mysql> show create table data_access;
| data_access | CREATE TABLE `data_access` (
`id` int(11) NOT NULL,
`name` varchar(45) DEFAULT NULL,
`group` varchar(100) DEFAULT NULL,
`gid` int(11) DEFAULT NULL COMMENT '例如微信数据',
`ext` text,
`data_schema_id` int(11) DEFAULT NULL COMMENT '绑定的schema_id\n',
`weight` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='数据接入方式 设置好的接入方式'
mysql> ALTER TABLE data_access ADD status tinyint(2) DEFAULT '2' ;
Query OK, 39 rows affected (0.01 sec)
Records: 39 Duplicates: 0 Warnings: 0
mysql> show create table data_access;
| data_access | CREATE TABLE `data_access` (
`id` int(11) NOT NULL,
`name` varchar(45) DEFAULT NULL,
`group` varchar(100) DEFAULT NULL,
`gid` int(11) DEFAULT NULL COMMENT '例如微信数据',
`ext` text,
`data_schema_id` int(11) DEFAULT NULL COMMENT '绑定的schema_id\n',
`weight` int(11) DEFAULT NULL,
`status` tinyint(2) DEFAULT '2',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='数据接入方式 设置好的接入方式'
33、mysql开启binglog
（1）关闭mysql
更安全的方法：
/opt/amos/mysql/bin/mysqladmin -uroot -p shutdown
（2）添加binglog配置
vim /etc/my.cnf
在[mysqld]下添加配置：
log_bin =/opt/amos/mysql/log/mysql-bin
binlog_format = MIXED ＃指定binglog格式
（3）重新启动mysql
/opt/amos/mysql/bin/mysqld_safe --defaults-file=/etc/my.cnf
34、mysql导出恢复数据表结构
在41上导出表结构：
mysqldump -uroot -p -h172.22.64.41 -d dmp_meta >dmp_meta.sql20170222
scp 到另一台服务器
scp dmp_meta.sql20170222 jingzan@10.8.8.240:/tmp
在240的服务器上登录mysql，新建数据库dmp_meta，然后source /tmp/dmp_meta.sql20170222，导入表结构
mysql> create database dmp_report DEFAULT CHARSET=utf8 ;
mysql> use dmp_meta;
mysql> source /tmp/dmp_meta.sql20170222;
35、mysql binlog删除处理，保留5天
（1）几种方法：
a.mysql>reset master(清空所有的二进制日志文件)
b.mysql>purge master logs to ‘bintest.000006′;(删除bintest.000006之前的二进制日志文件)
c.mysql>purge master logs before ’2007-08-10 04:07:00′(删除该日期之前的日志)
没有主从同步的情况下清理日志，手动删除5天前的MySQL binlog日志：
d.mysql> PURGE MASTER LOGS BEFORE DATE_SUB(CURRENT_DATE, INTERVAL 5 DAY);
e.mysql -uroot -p123456 -e 'PURGE MASTER LOGS BEFORE DATE_SUB( NOW( ),INTERVAL 5 DAY)';
f.修改/etc/my.cnf 自动清理方式
（2）最终以修改配置文件的方法重启mysql：
screen -R mysql-bin-deal-david
关闭mysql：/opt/amos/mysql/bin/mysqladmin -uroot -p shutdown
vi /etc/my.cnf #编辑配置
expire_logs_days = 5 #自动删除5天前的日志。默认值为0，表示从不删除。log-bin=mysql-bin #注释掉之后，会关闭binlog日志binlog_format=mixed #注释掉之后，会关闭binlog日志启动mysql：/opt/amos/mysql/bin/mysqld_safe --defaults-file=/etc/my.cnf
log文件明显减少了很多
[root@ZAMPDMP-01 mysql]# du -sh log
650G log
[root@ZAMPDMP-01 mysql]# du -sh log
91G log
36、mysql使用binlog恢复数据库
实例：今天同事误删除了mcddmphub01服务器上的schedule库中的schedule表中的部分数据，原因是删除操作的时候少加了一个过滤条件 and status<>4，应该删除4000多条数据，但删除了50000多条数据。错误操作时间2017/01/12 15:30
应该执行：delete from schedule.schedule where stat_date >'2016-11-18 23:00:00' and job_id = 275 and status<>4;
错误执行：delete from schedule.schedule where stat_date >'2016-11-18 23:00:00' and job_id = 275;
还好开启了mysql的binglog，并且每天晚上3点钟有数据库的全备份。那么怎么最安全的恢复数据呢？
方法是在新的测试服务器上恢复mysql库和表，找到删除的数据，然后插入到正式库中。
（1）在一台新的mysql上恢复schedule库中的schedule表，数据库恢复到时间2017/01/12 03:00
在mcddmpnode08上安装一个mysql，登录mysql，
create database schedule;
use schedule;
source /tmp/10.0.0.20_2017-01-12.schedule.sql;
（2）获取2017/01/12 03:00——2017/01/12 15:24的所有操作sql（开发操作在15:30），使用mysql binlog将mcddmpnode08测试数据库schedule恢复到开发删除数据之前，
[azureuser@mcddmphub01 ~]$ ll /opt/amos/mysql/log/
-rw-rw---- 1 amos amos 1073741886 Jan 9 05:20 mysql-bin.000022
-rw-rw---- 1 amos amos 1073742219 Jan 10 16:48 mysql-bin.000023
-rw-rw---- 1 amos amos 1073742066 Jan 12 04:26 mysql-bin.000024 ＃Jan 10 16:48——Jan 12 04:26的操作日志
-rw-rw---- 1 amos amos 964417880 Jan 13 11:22 mysql-bin.000025 ＃Jan 12 04:26——Jan 13 11:22的操作日志
如何指定bin log恢复sql命令的开始时间？可以通过03:00备份文件知道
[azureuser@mcddmphub01 mysqlfullbackup]$ cd /opt/amos/mysqlfullbackup
[azureuser@mcddmphub01 mysqlfullbackup]$ vim 10.0.0.20_2017-01-12.schedule.sql ＃查看备份文件偏移量
注意：CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000024', MASTER_LOG_POS=1030654544;
说明了该备份操作记录在 mysql-bin.000024中，偏移位：1030654544，对应任务计划中数据库备份时间：2017/01/12 03:00
＃2017/01/12 03:00—2017/01/12 04:26 sql操作
mysqlbinlog --start-position=1030654544 schedule mysql-bin.000024 > /tmp/schedule24.log
＃2017/01/12 04:26—2017-01-12 17:24:28 sql操作
mysqlbinlog --stop-datetime="2017-01-12 17:24:28" schedule mysql-bin.000025 > /tmp/schedule25.log
(3)恢复2017/01/12 03:00——2017/01/12 15:24的所有操作sql，在测试库schedule上source生成的两个文件，即可还原schedule到2017/01/12 15:24分,测试库恢复到2017/01/12 15:24ok
use schedule
source /tmp/schedule24.log
source /tmp/schedule25.log
（3）找到被错误删除的数据，使用mysqldump命令将mcddmpnode08所有schedule中删除的数据导出到sql文件
mysqldump -u用户名 -p密码 数据库名 表名 --where="筛选条件" > 导出文件路径
mysqldump -uroot -p schedule schedule --where="stat_date >'2016-11-18 23:00:00' and job_id = 275"> david-schedule20170112.sql
然后输入mysql的root用户的密码，即可倒出
注意：打开文件，发现有drop schedule if exit，这是非常危险的操作，必须删除这一行。
（4）还原被删除数据到原始库中，在mcddmphub01服务器上使用source命令恢复被删除schedule库中的数据，还原schedule数据到2017/01/12 15:24分
use schedule
source /tmp/david-schedule20170112.sql
（5）使用正确的命令执行开发的需求，注意添加了条件
delete from schedule.schedule where stat_date >'2016-11-18 23:00:00' and job_id = 275 and status<>4;
后记：不要给开发数据库权限，杀伤力极大。数据库一定要做好备份，否则后果不堪设想，对于运维来说，数据库在，人在，数据库亡，人亡！
