ITPub博客

首页 > 数据库 > MySQL > MySQL基本概念【一】

MySQL基本概念【一】

原创 MySQL 作者:哎呀我的天呐 时间:2015-07-25 13:42:07 0 删除 编辑
1.看表的DDL创建语句
show create table TABLE_NAME\G;

点击(此处)折叠或打开

  1. mysql> show create table dept_emp\G;
  2. *************************** 1. row ***************************
  3.        Table: dept_emp
  4. Create Table: CREATE TABLE `dept_emp` (
  5.   `emp_no` int(11) NOT NULL,
  6.   `dept_no` char(4) NOT NULL,
  7.   `from_date` date NOT NULL,
  8.   `to_date` date NOT NULL,
  9.   PRIMARY KEY (`emp_no`,`dept_no`),
  10.   KEY `emp_no` (`emp_no`),
  11.   KEY `dept_no` (`dept_no`),
  12.   CONSTRAINT `dept_emp_ibfk_1` FOREIGN KEY (`emp_no`) REFERENCES `employees` (`emp_no`) ON DELETE CASCADE,
  13.   CONSTRAINT `dept_emp_ibfk_2` FOREIGN KEY (`dept_no`) REFERENCES `departments` (`dept_no`) ON DELETE CASCADE
  14. ) ENGINE=InnoDB DEFAULT CHARSET=utf8
  15. 1 row in set (0.00 sec)

  16. ERROR:
  17. No query specified
2.在列上创建索引,和Oracle一样

点击(此处)折叠或打开

  1. mysql> create index emp_no_ind on dept_emp(emp_no);
  2. Query OK, 0 rows affected, 1 warning (2.37 sec)
  3. Records: 0 Duplicates: 0 Warnings: 1
很多书上说,在建立主键的时候要建立自增主键,为什么不同于Oracle,因为Oracle的表是堆表,大家都明白,Oracle的索引是。innodb的表是天然的树形结构,如果你不创建primary key 他也会给你创建,一个列上有唯一性的id,你看不见他,他是根据内存里面的一个值,然后递增生成的,他不是为单独的表递增的,他是为系统中所有的表递增,是个全局性total order的id,这样就成为一个热点,为了避免这种热点,我们在创建MySQL中的表的时候要指定递增唯一主键,所以MySQL中插入一条数据不是说找到一个空闲的内存区就放在上面,而是放在自己的B-Tree位置。是Oracle IOT表的概念。

InnoDB的一个page是16K,如果你的id列不是自增, 如果你插入了id = 1,id = 14,第一个id在B-Tree的最左端,机械盘对随机读的衰减的影响非常大。如果id相邻 1 2 3 那么一个page就搞定了即一个IO,这就是为什么要有自增索引, SSD减少随机读了,但不能消除。

3.重命名表

点击(此处)折叠或打开

  1. mysql> rename table test to test_bak;
  2. Query OK, 0 rows affected (0.01 sec)
4.添加列

点击(此处)折叠或打开

  1. mysql> alter table test add sal int;
  2. Query OK, 0 rows affected (0.07 sec)
  3. Records: 0 Duplicates: 0 Warnings: 0

  4. mysql> select * from test;
  5. +------+---------+------+
  6. | id |    ename |  sal |
  7. +------+---------+------+
  8. |  1 |  wuyusen | NULL |
  9. +------+---------+------+
在MySQL5.5之前,添加列,表中的数据会被影响,我现在是5.6.22表中有一行数据,在我添加列的时候,0 rows affected,5.5的话会有1 rows affected,5.5写入会被阻塞。

5.alter table drop index

6.index

点击(此处)折叠或打开

  1. mysql> select * from test where id = 1;
  2. +------+---------+------+
  3. | id | ename | sal |
  4. +------+---------+------+
  5. | 1 | wuyusen | NULL |
  6. +------+---------+------+
  7. 1 row in set (0.00 sec)

  8. mysql> select * from test limit 10\G;
  9. *************************** 1. row ***************************
  10.    id: 1
  11. ename: wuyusen
  12.   sal: NULL
  13. *************************** 2. row ***************************
  14.    id: 2
  15. ename: aaa
  16.   sal: NULL
  17. 2 rows in set (0.00 sec)

  18. ERROR:
  19. No query specified

  20. mysql> select * from test where id = 1\G;
  21. *************************** 1. row ***************************
  22.    id: 1
  23. ename: wuyusen
  24.   sal: NULL
  25. 1 row in set (0.00 sec)

  26. ERROR:
  27. No query specified

下面是

点击(此处)折叠或打开

  1. mysql> explain select * from test where id =1\G;
  2. *************************** 1. row ***************************
  3.            id: 1
  4.   select_type: SIMPLE
  5.         table: test
  6.          type: ref
  7. possible_keys: test_id_ind
  8.           key: test_id_ind
  9.       key_len: 5
  10.           ref: const
  11.          rows: 1
  12.         Extra: NULL
  13. 1 row in set (0.00 sec)

  14. ERROR:
  15. No query specified

点击(此处)折叠或打开

  1. mysql> explain select * from test where id = 1;
  2. +----+-------------+-------+------+---------------+-------------+---------+-------+------+-------+
  3. | id | select_type | table | type | possible_keys |      key    | key_len |   ref | rows | Extra |
  4. +----+-------------+-------+------+---------------+-------------+---------+-------+------+-------+
  5. |  1 |      SIMPLE |  test |  ref   test_id_ind | test_id_ind |       5 | const |    1 |  NULL |
  6. +----+-------------+-------+------+---------------+-------------+---------+-------+------+-------+
  7. 1 row in set (0.01 sec)
先做词法解析,把select * from test 每个单词,是否是符合语法,生成执行计划,这里的possible_keys是可能选择走的索引,如果有主键和一个索引的话,possible_keys就会有两个值,key上就会出现真实的走的是哪个,

7.将一个表导出来

点击(此处)折叠或打开

  1. mysqldump -h127.0.0.1 -uroot employees salaries > /media/employees.sql

点击(此处)折叠或打开

  1. [root@MysqlPri media]# more employees.sql
  2. -- MySQL dump 10.13 Distrib 5.6.22, for Linux (x86_64)
  3. --
  4. -- Host: 127.0.0.1 Database: employees
  5. -- ------------------------------------------------------
  6. -- Server version    5.6.22-log

  7. /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
  8. /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
  9. /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
  10. /*!40101 SET NAMES utf8 */;
  11. /*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
  12. /*!40103 SET TIME_ZONE='+00:00' */;
  13. /*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
  14. /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
  15. /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
  16. /*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

  17. --
  18. -- Table structure for table `salaries`
  19. --

  20. DROP TABLE IF EXISTS `salaries`;
  21. /*!40101 SET @saved_cs_client = @@character_set_client */;
  22. /*!40101 SET character_set_client = utf8 */;
  23. CREATE TABLE `salaries` (
  24.   `emp_no` int(11) NOT NULL,
  25.   `salary` int(11) NOT NULL,
  26.   `from_date` date NOT NULL,
  27.   `to_date` date NOT NULL,
  28.   PRIMARY KEY (`emp_no`,`from_date`),
  29.   KEY `emp_no` (`emp_no`),
  30.   CONSTRAINT `salaries_ibfk_1` FOREIGN KEY (`emp_no`) REFERENCES `employees` (`emp_no`) ON DELETE CASCADE
  31. ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
  32. /*!40101 SET character_set_client = @saved_cs_client */;

  33. --
  34. -- Dumping data for table `salaries`
  35. --

  36. LOCK TABLES `salaries` WRITE;
  37. /*!40000 ALTER TABLE `salaries` DISABLE KEYS */;
  38. INSERT INTO `salaries` VALUES (10001,60117,'1986-06-26','1987-06-26'),(10001,62102,'1987-06-26','1988-06-25'),(10001,66074,'1988-06-25','1989-06-25'),(10001,66596,'1989-06-25','1990-06-25'),(
  39. 10001,66961,'1990-06-25','1991-06-25'),(10001,71046,'1991-06-25','1992-06-24'),(10001,74333,'1992-06-24','1993-06-24'),(10001,75286,'1993-06-24','1994-06-24'),(10001,75994,'1994-06-24','1995-
  40. 06-24'),(10001,76884,'1995-06-24','1996-06-23'),(10001,80013,'1996-06-23','1997-06-23'),(10001,81025,'1997-06-23','1998-06-23'),(10001,81097,'1998-06-23','1999-06-23'),(10001,84917,'1999-06-2
  41. 3','2000-06-22'),(10001,85112,'2000-06-22','2001-06-22'),(10001,85097,'2001-06-22','2002-06-22'),(10001,88958,'2002-06-22','9999-01-01'),(10002,65828,'1996-08-03','1997-08-03'),(10002,65909,'
  42. 1997-08-03','1998-08-03'),(10002,67534,'1998-08-03','1999-08-03'),(10002,69366,'1999-08-03','2000-08-02'),(10002,71963,'2000-08-02','2001-08-02'),(10002,72527,'2001-08-02','9999-01-01'),(1000
  43. 3,40006,'1995-12-03','1996-12-02'),(10003,43616,'1996-12-02','1997-12-02'),(10003,43466,'1997-12-02','1998-12-02'),(10003,43636,'1998-12-02','1999-12-02'),(10003,43478,'1999-12-02','2000-12-0
  44. 1'),(10003,43699,'2000-12-01','2001-12-01'),(10003,43311,'2001-12-01','9999-01-01'),(10004,40054,'1986-12-01','1987-12-01'),(10004,42283,'1987-12-01','1988-11-30'),(10004,42542,'1988-11-30','
  45. 1989-11-30'),(10004,46065,'1989-11-30','1990-11-30'),(10004,48271,'1990-11-30','1991-11-30'),(10004,50594,'1991-11-30','1992-11-29'),(10004,52119,'1992-11-29','1993-11-29'),(10004,54693,'1993
  46. -11-29','1994-11-29'),(10004,58326,'1994-11-29','1995-11-29'),(10004,60770,'1995-11-29','1996-11-28'),(10004,62566,'1996-11-28','1997-11-28'),(10004,64340,'1997-11-28','1998-11-28'),(10004,67
  47. 096,'1998-11-28','1999-11-28'),(10004,69722,'1999-11-28','2000-11-27'),(10004,70698,'2000-11-27','2001-11-27'),(10004,74057,'2001-11-27','9999-01-01'),(10005,78228,'1989-09-12','1990-09-12'),
  48. (10005,82621,'1990-09-12','1991-09-12'),(10005,83735,'1991-09-12','1992-09-11'),(10005,85572,'1992-09-11','1993-09-11'),(10005,85076,'1993-09-11','1994-09-11'),(10005,86050,'1994-09-11','1995
  49. -09-11'),(10005,88448,'1995-09-11','1996-09-10'),(10005,88063,'1996-09-10','1997-09-10'),(10005,89724,'1997-09-10','1998-09-10'),(10005,90392,'1998-09-10','1999-09-10'),(10005,90531,'1999-09-
  50. 10','2000-09-09'),(10005,91453,'2000-09-09','2001-09-09'),(10005,94692,'2001-09-09','9999-01-01'),(10006,40000,'1990-08-05','1991-08-05'),(10006,42085,'1991-08-05','1992-08-04'),(10006,42629,
  51. '1992-08-04','1993-08-04'),(10006,45844,'1993-08-04','1994-08-04'),(10006,47518,'1994-08-04','1995-08-04'),(10006,47917,'1995-08-04','1996-08-03'),(10006,52255,'1996-08-03','1997-08-03'),(100
40101是版本兼容
直接source XX.sql 就能导入,不同的库,
也可以:
select * from test into outfile  '/backup/xx.sql';


8.数据迁移

方式一:停机时间少(简单)

如果5.5 迁移5.6那么搭建备库,master 5.5,slave 5.6接到主库5.5上。
设置read_only 为on,这样的你的库就是只读的了,root用户限制不住。主库没有写入,备库将bin-log应用完,然后将应用、DNS切换到备库。

5.5 master -->5.5 slave
  |
5.6 slave
 |

点击(此处)折叠或打开

  1. mysql> set global read_only=on

点击(此处)折叠或打开

  1. mysql> show variables like '%read%';
  2. ERROR 2006 (HY000): MySQL server has gone away
  3. No connection. Trying to reconnect...
  4. Connection id: 23
  5. Current database: test

  6. +-----------------------------------------+---------------------------+
  7. | Variable_name                           |                     Value |
  8. +-----------------------------------------+---------------------------+
  9. | innodb_purge_threads                    |                         1 |
  10. | innodb_random_read_ahead                |                       OFF |
  11. | innodb_read_ahead_threshold             |                        56 |
  12. | innodb_read_io_threads                  |                         4 |
  13. | innodb_read_only                        |                       OFF |
  14. | innodb_thread_concurrency               |                         0 |
  15. | innodb_thread_sleep_delay               |                     10000 |
  16. | innodb_write_io_threads                 |                         4 |
  17. | max_delayed_threads                     |                        20 |
  18. | max_insert_delayed_threads              |                        20 |
  19. | myisam_repair_threads                   |                         1 |
  20. | net_read_timeout                        |                        30 |
  21. | performance_schema_max_thread_classes   |                        50 |
  22. | performance_schema_max_thread_instances |                       402 |
  23. | pseudo_thread_id                        |                        23 |
  24. | read_buffer_size                        |                    131072 |
  25. | read_only                               |                       OFF |
  26. | read_rnd_buffer_size                    |                  33554432 |
  27. | thread_cache_size                       |                         9 |
  28. | thread_concurrency                      |                        10 |
  29. | thread_handling                         | one-thread-per-connection |
  30. | thread_stack                            |                    262144 |
  31. | tx_read_only                            |                       OFF |
  32. +-----------------------------------------+---------------------------+
  33. 23 rows in set (0.00 sec)

9.bin log

点击(此处)折叠或打开

  1. mysql> show binlog events

点击(此处)折叠或打开

  1. mysql> reset master;
  2. Query OK, 0 rows affected (0.07 sec)

  3. mysql> show binlog events;
  4. +-----------------+-----+-------------+-----------+-------------+---------------------------------------+
  5. | Log_name        | Pos |  Event_type | Server_id | End_log_pos | Info                                  |
  6. +-----------------+-----+-------------+-----------+-------------+---------------------------------------+
  7. | mybinlog.000001 |   4 | Format_desc |    203306 |         120 | Server ver: 5.6.22-log, Binlog ver: 4 |
  8. +-----------------+-----+-------------+-----------+-------------+---------------------------------------+
  9. 1 row in set (0.00 sec)
reset master是清理bin log的,下面我们创建一个表再看bin log

点击(此处)折叠或打开

  1. mysql> create table xx (id int);
  2. Query OK, 0 rows affected (0.03 sec)

  3. mysql> show binlog master;
  4. 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 'master' at line 1
  5. mysql> show binlog events;
  6. +-----------------+-----+-------------+-----------+-------------+---------------------------------------+
  7. |        Log_name | Pos |  Event_type | Server_id | End_log_pos | Info                                  |
  8. +-----------------+-----+-------------+-----------+-------------+---------------------------------------+
  9. | mybinlog.000001 |   4 | Format_desc |    203306 |         120 | Server ver: 5.6.22-log, Binlog ver: 4 |
  10. | mybinlog.000001 | 120 |       Query |    203306 |         218 |  use `test`; create table xx (id int) |
  11. +-----------------+-----+-------------+-----------+-------------+---------------------------------------+
  12. 2 rows in set (0.00 sec)
第二行就是创建xx表,记录在bin log中的内容。
120字节的位置,多了个query 操作,相当Oracle redo log。

点击(此处)折叠或打开

  1. [root@MysqlPri logs]# mysqlbinlog -vvv mybinlog.000001 > /media/log.log
  2. [root@MysqlPri logs]# more /media/log.log
  3. /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
  4. /*!40019 SET @@session.max_insert_delayed_threads=0*/;
  5. /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
  6. DELIMITER /*!*/;
  7. # at 4
  8. #150724 6:30:41 server id 203306 end_log_pos 120 CRC32 0x3d6c3f2f     Start: binlog v 4, server v 5.6.22-log created 150724 6:30:41 at startup
  9. # Warning: this binlog is either in use or was not closed properly.
  10. ROLLBACK/*!*/;
  11. BINLOG '
  12. EWuxVQ8qGgMAdAAAAHgAAAABAAQANS42LjIyLWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
  13. AAAAAAAAAAAAAAAAAAARa7FVEzgNAAgAEgAEBAQEEgAAXAAEGggAAAAICAgCAAAACgoKGRkAAS8/
  14. bD0=
  15. '/*!*/;
  16. # at 120
  17. #150724 6:32:46 server id 203306 end_log_pos 218 CRC32 0x5e7a611a     Query    thread_id=23    exec_time=0    error_code=0
  18. use `test`/*!*/;
  19. SET TIMESTAMP=1437690766/*!*/;
  20. SET @@session.pseudo_thread_id=23/*!*/;
  21. SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
  22. SET @@session.sql_mode=1075838976/*!*/;
  23. SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
  24. /*!\C utf8 *//*!*/;
  25. SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=33/*!*/;
  26. SET @@session.lc_time_names=0/*!*/;
  27. SET @@session.collation_database=DEFAULT/*!*/;
  28. create table xx (id int)
  29. /*!*/;
  30. DELIMITER ;
  31. # End of log file
  32. ROLLBACK /* added by mysqlbinlog */;
  33. /*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
  34. /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/
也可以用insert 看看,实验下这里就不做了,

将当前session sql的binlog关闭

点击(此处)折叠或打开

  1. mysql> set session sql_log_bin=off;
  2. Query OK, 0 rows affected (0.00 sec)
与nologging还不太一样,这个binlog是完完全全的不记录日志。

切换日志,现在已经变成002了... ...

点击(此处)折叠或打开

  1. mysql> flush logs;
  2. Query OK, 0 rows affected (0.02 sec)

  3. mysql>
  4. mysql> show master status;
  5. +-----------------+----------+--------------+------------------+-------------------+
  6. | File            | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
  7. +-----------------+----------+--------------+------------------+-------------------+
  8. | mybinlog.000002 |      120 |              |                  |                   |
  9. +-----------------+----------+--------------+------------------+-------------------+
  10. 1 row in set (0.04 sec)
这个server 层的redo ,他是不能循环利用的,类似于Oracle的Archive Log。InnoDB有自己的redo,其中ibdata1是InnoDB的元数据,ib_logfile1、ib_logfile1 默认在datadir下,我们也可以指定其位置,这个是循环应用的,0用完用1,1用完用0,类似Redo但不完全相同。InnoDB log不归档,Oracle的redo只有一份即用来做实例的recovery,又用来备库、备份,但是InnoDB的iblog只能用来实例恢复,只保证InnoDB层不丢数据,至于你后续更改了什么,那是server层记录的日志,这就是inno层,server层的不同,也是为什么mysql多种引擎,InnoDB他脱离的MySQL能是个数据库,最初InnoDB就是个数据库,后来InnoDB发展不好,同时MySQL的server层接口很好就结合在一起,这就是为什么有两个日志。你可以这么理解,server层的bin log只记录理解为DML,DDL用户的操作,那么InnoDB的iblog就是记录这些操作怎么存储,你是转换成什么字节,什么编码二进制,server层不管你plugin了什么引擎,只要你能给我存进去就行.... ....

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/29990276/viewspace-1751252/,如需转载,请注明出处,否则将追究法律责任。

上一篇: 12c
请登录后发表评论 登录
全部评论
从事Oracle/MySQL工作多年,11g OCM,擅长Oracle/MySQL SQL Tuning & DB Performance,高可用方案架构

注册时间:2014-10-30

  • 博文量
    253
  • 访问量
    1831026