库 --> 表 --> 行 --> 字段
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| test |
| ddb1 |
| discuz |
| mysql |
| test |
+--------------------+
6 rows in set (0.00 sec)
mysql> use mysql;
Database changed
mysql> select database;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1
mysql> select database();
+------------+
| database() |
+------------+
| mysql |
+------------+
1 row in set (0.00 sec)
mysql> select user();
+----------------+
| user() |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.00 sec)
mysql> select version();
+------------+
| version() |
+------------+
| 5.1.73-log |
+------------+
1 row in set (0.00 sec)
mysql> use discuz
Database changed
mysql> show tables;
+-----------------------------------+
| Tables_in_discuz |
+-----------------------------------+
| pre_common_admincp_cmenu |
| pre_common_admincp_group |
| pre_common_admincp_member |
| pre_common_admincp_perm |
| pre_common_admincp_session
mysql> show create table pre_ucenter_vars\G;
*************************** 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=utf8
1 row in set (0.00 sec)
mysql> desc pre_forum_post;
+-------------+-----------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+-----------------------+------+-----+---------+----------------+
| pid | int(10) unsigned | NO | UNI | NULL | |
| fid | mediumint(8) unsigned | NO | MUL | 0 | |
| tid | mediumint(8) unsigned | NO | PRI | 0 | |
| first | tinyint(1) | NO | | 0 | |
| author | varchar(15) | NO | | | |
mysql> create table tb1 (`id` int(4), `name` char(40));
Query OK, 0 rows affected (0.01 sec)
mysql> show tables;
+------------------+
| Tables_in_test1 |
+------------------+
| tb1 |
+------------------+
1 row in set (0.00 sec)
mysql> desc tab1;
ERROR 1146 (42S02): Table 'test1.tab1' doesn't exist
mysql> desc tb1;
+-------+----------+------+-----+---------+-------+
| 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;
*************************** 1. row ***************************
Table: tb1
Create Table: CREATE TABLE `tb1` (
`id` int(4) DEFAULT NULL,
`name` char(40) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
ERROR:
No query specified
mysql> insert into tb1 values(1, 'test');
Query OK, 1 row affected (0.00 sec)
mysql> select * from tb1;
+------+-------+
| id | name |
+------+-------+
| 1 | test |
+------+-------+
1 row in set (0.00 sec)
mysql> insert into tb1 values(2, 'linux');
Query OK, 1 row affected (0.00 sec)
mysql> select * from tb1;
+------+-------+
| id | name |
+------+-------+
| 1 | test |
| 2 | linux |
+------+-------+
2 rows in set (0.00 sec)
mysql> insert into tb1 (`id`) values(2);
Query OK, 1 row affected (0.00 sec)
mysql> select * from tb1;
+------+-------+
| id | name |
+------+-------+
| 1 | test |
| 2 | linux |
| 2 | NULL |
+------+-------+
3 rows in set (0.00 sec)
mysql> insert into tb1 (`id`) values(4);
Query OK, 1 row affected (0.00 sec)
mysql> select * from tb1;
+------+-------+
| id | name |
+------+-------+
| 1 | test |
| 2 | linux |
| 2 | NULL |
| 4 | NULL |
+------+-------+
4 rows in set (0.00 sec)
mysql> insert into tb1 (`name`) values(55);
Query OK, 1 row affected (0.00 sec)
mysql> select * from tb1;
+------+-------+
| id | name |
+------+-------+
| 1 | test |
| 2 | linux |
| 2 | NULL |
| 4 | NULL |
| NULL | 55 |
+------+-------+
5 rows in set (0.00 sec)
mysql> insert into tb1 (`name`, `id`) values(55,6);
Query OK, 1 row affected (0.00 sec)
mysql> select * from tb1;
+------+-------+
| id | name |
+------+-------+
| 1 | test |
| 2 | linux |
| 2 | NULL |
| 4 | NULL |
| NULL | 55 |
| 6 | 55 |
+------+-------+
6 rows in set (0.00 sec)
mysql> update tb1 set id=5 where name = '55';
Query OK, 2 rows affected (0.00 sec)
Rows matched: 2 Changed: 2 Warnings: 0
mysql> select * from tb1;
+------+-------+
| id | name |
+------+-------+
| 1 | test |
| 2 | linux |
| 2 | NULL |
| 4 | NULL |
| 5 | 55 |
| 5 | 55 |
+------+-------+
6 rows in set (0.00 sec)
mysql> delete from tb1 where name='55';
Query OK, 2 rows affected (0.00 sec)
mysql> select * from tb1;
+------+-------+
| id | name |
+------+-------+
| 1 | test |
| 2 | linux |
| 2 | NULL |
| 4 | NULL |
+------+-------+
4 rows in set (0.00 sec)
mysql> truncate table test1.tb1;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from tb1;
Empty set (0.00 sec)
mysql> drop table tb1;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from tb1;
ERROR 1146 (42S02): Table 'test1.tb1' doesn't exist
mysql> drop database test1;
Query OK, 0 rows affected (0.06 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| test |
| ddb1 |
| discuz |
| mysql |
| test |
+--------------------+
6 rows in set (0.00 sec)
mysql> grant all on discuz.* to 'user1'@'192.168.11.%' identified by 'dsafasdff';
Query OK, 0 rows affected (0.00 sec)
mysql> flush privileges;刷新权限
Query OK, 0 rows affected (0.00 sec)
mysql> show processlist;查看队列
+----+------+-----------+------+---------+------+-------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+-----------+------+---------+------+-------+------------------+
| 12 | root | localhost | NULL | Query | 0 | NULL | show processlist |
+----+------+-----------+------+---------+------+-------+------------------+
1 row in set (0.00 sec)
mysql> show variables;查看变量
+-----------------------------------------+-------------------------------------------------------------------------------------------+
| Variable_name | Value |
+-----------------------------------------+-------------------------------------------------------------------------------------------+
| auto_increment_increment | 1 |
| auto_increment_offset | 1 |
| autocommit | ON |
| automatic_sp_privileges | ON |
| back_log | 50 |
| basedir | /usr/local/mysql/ |
| big_tables | OFF |
| binlog_cache_size | 32768 |
| binlog_direct_non_transactional_updates | OFF |
| binlog_format | MIXED |
| bulk_insert_buffer_size | 8388608 |
| character_set_client | latin1 |
| character_set_connection | latin1 |
| character_set_database | latin1 |
| character_set_filesystem | binary |
| character_set_results | latin1
mysql> set global max_connections=200;
Query OK, 0 rows affected (0.00 sec)
mysql> show variables like 'max_conne%';
+--------------------+-------+
| Variable_name | Value |
+--------------------+-------+
| max_connect_errors | 1000 |
| max_connections | 200 |
+--------------------+-------+
2 rows in set (0.00 sec)
mysql> set global max_connect_errors=100;
Query OK, 0 rows affected (0.00 sec)
mysql> show variables like 'max_conne%';
+--------------------+-------+
| Variable_name | Value |
+--------------------+-------+
| max_connect_errors | 100 |
| max_connections | 200 |
+--------------------+-------+
2 rows in set (0.00 sec)
mysql> show status;查看状态
+-----------------------------------+-----------+
| Variable_name | Value |
+-----------------------------------+-----------+
| Aborted_clients | 0 |
| Aborted_connects | 2 |
| Binlog_cache_disk_use | 0 |
| Binlog_cache_use | 0 |
| Bytes_received | 401 |
| Bytes_sent | 8913 |
mysql> show status like '%running';
+-----------------+-------+
| Variable_name | Value |
+-----------------+-------+
| Slave_running | OFF |
| Threads_running | 1 |
+-----------------+-------+
2 rows in set (0.00 sec)
mysql> show status like '%buffor%';
Empty set (0.00 sec)
mysql> show status like '%buff%';
+-----------------------------------+-------+
| 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)
mysql> repair table discuz.pre_forum_post; 修复表
+-----------------------+--------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+-----------------------+--------+----------+----------+
| discuz.pre_forum_post | repair | status | OK |
+-----------------------+--------+----------+----------+
1 row in set (0.00 sec)
mysqldump -uroot -ptest1 discuz > /data/discuz.sql
mysql -uroot -ptest1 discuz < /data/discuz.sql
mysqldump -uroot -ptest1 discuz pre_forum_post > /data/post.sql
mysql -uroot -ptest1 discuz < /data/post.sql
mysqldump -uroot --default-character-set-gbk -ptest1 discuz > /data/discuz.sql
mysql -uroot --default-character-set-gbk -ptest1 discuz < /data/discuz.sql
浙公网安备 33010602011771号