MySQL常用操作

回复 收藏
MySQL常用操作
查看库
mysql> show databases;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    18
Current database: *** NONE ***

+--------------------+
| Database           |
+--------------------+
| information_schema |
| discuz             |
| mysql              |
| test               |
+--------------------+
4 rows in set (0.01 sec)

切换库
mysql> use discuz
No connection. Trying to reconnect...
Connection id:    19
Current database: *** NONE ***

Database changed

查看当前是在哪个库
mysql> select database();
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    20
Current database: discuz

+------------+
| database() |
+------------+
| discuz     |
+------------+
1 row in set (0.00 sec)
mysql>
查看哪个用户

mysql> select user();
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    21
Current database: discuz

+----------------+
| user()         |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.00 sec)

mysql>

查看版本
mysql> select version();
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    22
Current database: discuz

+------------+
| version()  |
+------------+
| 5.1.73-log |
+------------+
1 row in set (0.00 sec)

mysql>

查看库里面的表
mysql> show tables;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    23
Current database: discuz

+-----------------------------------+
| Tables_in_discuz                  |
+-----------------------------------+
| pre_common_admincp_cmenu          |
| pre_common_admincp_group          |
| pre_common_admincp_member         |
| pre_common_admincp_perm           |
| pre_common_admincp_session        |
| pre_common_admingroup             |
| pre_common_adminnote              |
| pre_common_advertisement          |
| pre_common_advertisement_custom   |
| pre_common_banned                 |
| pre_common_block                  |

+-----------------------------------+
297 rows in set (0.01 sec)

mysql> Ctrl-C -- exit!
Aborted

查看表有哪些字段(pre_ucenter_vars为表名
mysql> desc pre_ucenter_vars;
+-------+-----------+------+-----+---------+-------+
| Field | Type      | Null | Key | Default | Extra |
+-------+-----------+------+-----+---------+-------+
| name  | char(32)  | NO   | PRI |         |       |
| value | char(255) | NO   |     |         |       |
+-------+-----------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql>

查看怎么创建的表( pre_ucenter_vars为表名
mysql> show create table  pre_ucenter_vars\G;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    32
Current database: discuz

*************************** 1. row ***************************
       Table: pre_ucenter_vars
Create Table: CREATE TABLE `pre_ucenter_vars` (
  `name` char(32) NOT NULL DEFAULT '',
  `value` char(255) NOT NULL DEFAULT '',
  PRIMARY KEY (`name`)
) ENGINE=MEMORY DEFAULT CHARSET=gbk
1 row in set (0.00 sec)

ERROR:
No query specified

mysql>

创建库
mysql> create database zhangpengpeng;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    34
Current database: discuz

Query OK, 1 row affected (0.00 sec)

mysql>

创建表(id:格式 int4:长度为4位,name:格式char40:最长40,DEFAULT CHARSET:字符集 tb1:表名
mysql> create table tb1 (`id` int(4),`name`char(40)) ENGINE=MYISAM DEFAULT CHARSET=gbk;  
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    38
Current database: zhangpengpeng

Query OK, 0 rows affected (0.00 sec)

mysql>
查看刚刚创建的库和表
mysql> show tables;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    39
Current database: zhangpengpeng

+-------------------------+
| Tables_in_zhangpengpeng |
+-------------------------+
| tb1                     |
+-------------------------+
1 row in set (0.00 sec)

mysql> desc tb1;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    40
Current database: zhangpengpeng

+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| id    | int(4)   | YES  |     | NULL    |       |
| name  | char(40) | YES  |     | NULL    |       |
+-------+----------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> show create table tb1\G;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    41
Current database: zhangpengpeng

*************************** 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)

ERROR:
No query specified

mysql>
创建表的内容
mysql> insert into tb1 values(1,'zhangpengpeng');
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    42
Current database: zhangpengpeng

Query OK, 1 row affected (0.00 sec)


mysql> select * from tb1;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    44
Current database: zhangpengpeng

+------+---------------+
| id   | name          |
+------+---------------+
|    1 | zhangpengpeng |
+------+---------------+
1 row in set (0.00 sec)

mysql>



单独创建一个内容
mysql> insert into tb1 (`id`) values(2);
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    45
Current database: zhangpengpeng

Query OK, 1 row affected (0.00 sec)

mysql> select * from tb1;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    46
Current database: zhangpengpeng

+------+---------------+
| id   | name          |
+------+---------------+
|    1 | zhangpengpeng |
|    2 | NULL          |
+------+---------------+
2 rows in set (0.00 sec)

mysql> insert into tb1 (`name`) values('100');
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    47
Current database: zhangpengpeng

Query OK, 1 row affected (0.00 sec)

mysql> insert into tb1 (`name`) values('linux');
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    48
Current database: zhangpengpeng

Query OK, 1 row affected (0.00 sec)

mysql> select * from tb1;
+------+---------------+
| id   | name          |
+------+---------------+
|    1 | zhangpengpeng |
|    2 | NULL          |
| NULL | 100           |
| NULL | linux         |
+------+---------------+
4 rows in set (0.00 sec)

mysql>


反过来创建
mysql> insert into tb1 (`name`,`id`) values('zhangsan','10');
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    50
Current database: zhangpengpeng

Query OK, 1 row affected (0.00 sec)

mysql> select * from tb1;
+------+---------------+
| id   | name          |
+------+---------------+
|    1 | zhangpengpeng |
|    2 | NULL          |
| NULL | 100           |
| NULL | linux         |
|   10 | zhangsan      |
+------+---------------+
5 rows in set (0.00 sec)


更新数据

mysql> select * from tb1;
+------+---------------+
| id   | name          |
+------+---------------+
|    1 | zhangpengpeng |
|    2 | NULL          |
| NULL | 100           |
| NULL | linux         |
|   10 | zhangsan      |
+------+---------------+
5 rows in set (0.00 sec)

mysql> update tb1 set id=10 where name ='linux';
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    52
Current database: zhangpengpeng

Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from tb1;
+------+---------------+
| id   | name          |
+------+---------------+
|    1 | zhangpengpeng |
|    2 | NULL          |
| NULL | 100           |
|   10 | linux         |
|   10 | zhangsan      |
+------+---------------+
5 rows in set (0.00 sec)

删除指定的行
mysql> select * from tb1;
+------+---------------+
| id   | name          |
+------+---------------+
|    1 | zhangpengpeng |
|    2 | NULL          |
| NULL | 100           |
|   10 | linux         |
|   10 | zhangsan      |
+------+---------------+
5 rows in set (0.00 sec)

mysql> delete from tb1 where name='zhangsan' ;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    53
Current database: zhangpengpeng

Query OK, 1 row affected (0.00 sec)

mysql> select * from tb1;
+------+---------------+
| id   | name          |
+------+---------------+
|    1 | zhangpengpeng |
|    2 | NULL          |
| NULL | 100           |
|   10 | linux         |
+------+---------------+
4 rows in set (0.00 sec)


清空表的内容,但是不删除表(zhangpengpeng:库名。tb1:表名

mysql> truncate zhangpengpeng.tb1;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    54
Current database: zhangpengpeng

Query OK, 0 rows affected (0.00 sec)

mysql> select * from tb1;
Empty set (0.00 sec)


删除表

mysql> drop table tb1;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    55
Current database: zhangpengpeng

Query OK, 0 rows affected (0.00 sec)

mysql> select * from tb1;
ERROR 1146 (42S02): Table 'zhangpengpeng.tb1' doesn't exist
mysql>



删除库
mysql> drop database zhangpengpeng;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    56
Current database: zhangpengpeng

Query OK, 0 rows affected (0.00 sec)

mysql> show databases;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    57
Current database: *** NONE ***

+--------------------+
| Database           |
+--------------------+
| information_schema |
| discuz             |
| mysql              |
| test               |
+--------------------+
4 rows in set (0.00 sec)


授权一个网段
mysql> grant all on discuz.* to 'user1'@192.168.0.%' identified by '密码';



刷新权限
mysql>mysql> flush privileges;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    58
Current database: *** NONE ***

Query OK, 0 rows affected (0.00 sec)


查询数据库有哪些队列
mysql> show processlist;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    59
Current database: *** NONE ***

+----+------+-----------+------+---------+------+-------+------------------+
| Id | User | Host      | db   | Command | Time | State | Info             |
+----+------+-----------+------+---------+------+-------+------------------+
| 59 | root | localhost | NULL | Query   |    0 | NULL  | show processlist |
+----+------+-----------+------+---------+------+-------+------------------+
1 row in set (0.00 sec)



查询变量
mysql> show variables;

MySQL服务正在运行,不想重新启动在MySQL执行更改参数。(set global max_connections=200 :更改的参数,重启不生效,重启生效改配置文件)
mysql> set global max_connections=200 ;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    62
Current database: *** NONE ***

Query OK, 0 rows affected (0.00 sec)



mysql> set global  max_connect_errors=100 ;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    67
Current database: *** NONE ***


Query OK, 0 rows affected (0.00 sec)


过滤参数(%:通配符,'max_connec%:过滤的内容
mysql> show variables like 'max_connec%';
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    64
Current database: *** NONE ***

+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| max_connect_errors | 10    |
| max_connections    | 200   |
+--------------------+-------+
2 rows in set (0.00 sec)


查看一些状态
mysql> show status;



用统配去过滤
mysql> show status like '%running';
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    69
Current database: *** NONE ***

+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| Slave_running   | OFF   |
| Threads_running | 1     |
+-----------------+-------+
2 rows in set (0.00 sec)



前后统配
mysql> show status like '%buffer%' ;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    72
Current database: *** NONE ***

+-----------------------------------+-------+
| Variable_name                     | Value |
+-----------------------------------+-------+
| Innodb_buffer_pool_pages_data     | 19    |
| Innodb_buffer_pool_pages_dirty    | 0     |
| Innodb_buffer_pool_pages_flushed  | 1     |
| Innodb_buffer_pool_pages_free     | 493   |
| Innodb_buffer_pool_pages_misc     | 0     |
| Innodb_buffer_pool_pages_total    | 512   |
| Innodb_buffer_pool_read_ahead_rnd | 1     |
| Innodb_buffer_pool_read_ahead_seq | 0     |
| Innodb_buffer_pool_read_requests  | 84    |
| Innodb_buffer_pool_reads          | 12    |
| Innodb_buffer_pool_wait_free      | 0     |
| Innodb_buffer_pool_write_requests | 1     |
+-----------------------------------+-------+
12 rows in set (0.00 sec)


查看错误日志
[root@Pengpeng mysql]# tail /data/mysql/Pengpeng.err
160827 21:23:02 mysqld_safe Starting mysqld daemon with databases from /data/mysql
160827 21:23:02 [Warning] '--skip-locking' is deprecated and will be removed in a future release. Please use '--skip-external-locking' instead.
160827 21:23:02 [Warning] '--log_slow_queries' is deprecated and will be removed in a future release. Please use ''--slow_query_log'/'--slow_query_log_file'' instead.
160827 21:23:02 [Note] Plugin 'FEDERATED' is disabled.
160827 21:23:02  InnoDB: Initializing buffer pool, size = 8.0M
160827 21:23:02  InnoDB: Completed initialization of buffer pool
160827 21:23:02  InnoDB: Started; log sequence number 0 44233
160827 21:23:02 [Note] Event Scheduler: Loaded 0 events
160827 21:23:02 [Note] /usr/local/mysql/bin/mysqld: ready for connections.
Version: '5.1.73-log'  socket: '/tmp/mysql.sock'  port: 3306  MySQL Community Server (GPL)
[root@Pengpeng mysql]#


修复一个表
mysql> repair table discuz.pre_forum_post;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    76
Current database: *** NONE ***

+-----------------------+--------+----------+----------+
| Table                 | Op     | Msg_type | Msg_text |
+-----------------------+--------+----------+----------+
| discuz.pre_forum_post | repair | status   | OK       |
+-----------------------+--------+----------+----------+
1 row in set (0.00 sec)

mysql>

MySQL备份与恢复(root:用户名mysql:密码)
备份
[root@Pengpeng ~]# mysqldump -u root -pmysql discuz
[root@Pengpeng ~]# mysqldump -uroot -pmysql discuz > /data/discuz.sql
恢复数据库
[root@Pengpeng data]# mysql -uroot -pmysql discuz < /data/discuz.sql
备份表
[root@Pengpeng data]# mysqldump -uroot -pmysql discuz pre_forum_post > /data/post.sql
恢复表(只需要跟库名,无需跟表名)
[root@Pengpeng data]# mysql -uroot -pmysql discuz < /data/post.sql
备份指定字符集
[root@Pengpeng data]# mysqldump -uroot --default-character-set=utf8 -pmysql discuz pre_forum_post > /data/post.sql
恢复也指定字符集
[root@Pengpeng data]# mysql -uroot --default-character-set=utf8 -pmysql discuz < /data/post.sql  

2016-08-28 14:31 举报
已邀请:

回复帖子,请先登录注册

退出全屏模式 全屏模式 回复
评分
可选评分理由: