xiayu 发表于 2018-10-2 11:15:59

mysql的常用操作

  参考:
  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;
  +------+---------+

  |>  +------+---------+
  |    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.%'>  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
  # vim /etc/init.d/mysqld
  datadir=/data/mysql
  # cd /data/mysql
  # ls
  daixuan.err
  # tail daixuan.err 查看错误日志
  22、如何修复表
  mysql>repair table discuz.pre_forum_post;
  23、mysql数据库备份
  # mysqldump -uroot -pdaixuan discuz
  # mysqldump -uroot -pdaixuan discuz > /data/discuz.sql
  # vim !$
  vim /data/discuz.sql
  24、mysql数据库恢复
  如果不小心删除了discuz的某一个表
  # cd /data/mysql/discuz/
  # rm -rf pre_forum_post*
  # /etc/init.d/mysqld restart
  Shutting down MySQL.... SUCCESS!
  Starting MySQL.. SUCCESS!
  这个时候网站已经不能正常打开了
  怎么恢复呢?
  # mysql -uroot -pdaixuan discuz < /data/discuz.sql
  25、只备份mysql数据库中的表
  # mysqldump -uroot -pdaixuan discuz pre_forum_post > /data/discuz.post.sql
  # vim !$
  vim /data/discuz.post.sql
  26、恢复mysql数据库中的表,注:恢复只加数据库名,不加表名
  # mysql -uroot -pdaixuan discuz < /data/discuz.post.sql
  27、备份和还原数据库指定字符集
  # mysqldump -uroot -pdaixuan --default-character-set=gbk discuz pre_forum_post > /data/discuz.post.sql
  # mysql -uroot -pdaixuan --default-character-set=gbk discuz < /data/discuz.post.sql
  28、导入本地sql文件,--local-infile=1
  # mysql -uroot -p --local-infile=1
  Enter password:
  Welcome to the MySQL monitor.Commands end with ; or \g.

  Your MySQL connection>  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: 371Duplicates: 0Warnings: 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记录,瞬间增加这么快。
  # du -sh DMP-GATEWAY.log
  107MDMP-GATEWAY.log
  # du -sh DMP-GATEWAY.log
  148MDMP-GATEWAY.log
  # du -sh DMP-GATEWAY.log
  277MDMP-GATEWAY.log
  # 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>>  Query OK, 39 rows affected (0.01 sec)
  Records: 39Duplicates: 0Warnings: 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
  在下添加配置:
  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_reportDEFAULT 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文件明显减少了很多
  # du -sh log
  650Glog
  # du -sh log
  91Glog
  36、mysql使用binlog恢复数据库
  实例:今天同事误删除了mcddmphub01服务器上的schedule库中的schedule表中的部分数据,原因是删除操作的时候少加了一个过滤条件 and status4,应该删除4000多条数据,但删除了50000多条数据。错误操作时间2017/01/12 15:30
  应该执行:delete from schedule.schedule where stat_date >'2016-11-18 23:00:00' and job_id = 275and status4;
  错误执行: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恢复到开发删除数据之前,
  $ ll /opt/amos/mysql/log/
  -rw-rw---- 1 amos amos 1073741886 Jan9 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 amos964417880 Jan 13 11:22 mysql-bin.000025   #Jan 12 04:26——Jan 13 11:22的操作日志
  如何指定bin log恢复sql命令的开始时间?可以通过03:00备份文件知道
  $ cd /opt/amos/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 schedulemysql-bin.000024 > /tmp/schedule24.log
  #2017/01/1204:26—2017-01-12 17:24:28 sql操作
  mysqlbinlog --stop-datetime="2017-01-12 17:24:28" schedulemysql-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 = 275and status4;
  后记:不要给开发数据库权限,杀伤力极大。数据库一定要做好备份,否则后果不堪设想,对于运维来说,数据库在,人在,数据库亡,人亡!

页: [1]
查看完整版本: mysql的常用操作