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
查看库
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
编辑回复