mcd 登陆mysql_mysql的常用操作

参考：

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

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>purgemaster logsto‘bintest.000006′;(删除bintest.000006之前的二进制日志文件)c.mysql>purgemaster logsbefore’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  #编辑配置

启动mysql：/opt/amos/mysql/bin/mysqld_safe --defaults-file=/etc/my.cnf

log文件明显减少了很多

[root@ZAMPDMP-01 mysql]# du -sh log

650Glog

[root@ZAMPDMP-01 mysql]# du -sh log

91Glog

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=1030654544schedule  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;

后记：不要给开发数据库权限，杀伤力极大。数据库一定要做好备份，否则后果不堪设想，对于运维来说，数据库在，人在，数据库亡，人亡！