Focus On Oracle

Installing, Backup & Recovery, Performance Tuning,
Troubleshooting, Upgrading, Patching

Oracle Engineered System


当前位置: 首页 » 技术文章 » MySQL

MySQL的基本架构

从2001年开始在使用InnoDB存储引擎,开始支持事务,MySQL也逐渐开始增加企业级环境所需要的特性。MySQL支持以下的环境:Linux,Windows,AIX,Solaris,HPUX

安装MySQL

最好通过源码安装,详见从源码安装文档

cmake \
-DCMAKE_INSTALL_PREFIX=/pohs/oracle/mysql/5.6 \
-DMYSQL_DATADIR=/pohs/oracle/mysql/data \
-DSYSCONFDIR=/etc \
-DWITH_MYISAM_STORAGE_ENGINE=1 \
-DWITH_INNOBASE_STORAGE_ENGINE=1 \
-DWITH_MEMORY_STORAGE_ENGINE=1 \
-DWITH_READLINE=1 \
-DMYSQL_UNIX_ADDR=/var/lib/mysql/mysql.sock \
-DMYSQL_TCP_PORT=3306 \
-DENABLED_LOCAL_INFILE=1 \
-DWITH_PARTITION_STORAGE_ENGINE=1 \
-DEXTRA_CHARSETS=all \
-DDEFAULT_CHARSET=utf8 \
-DDEFAULT_COLLATION=utf8_general_ci

所有的版本都包含以下特性:
   可插拔式Storage Engine Architecture
   多种存储引擎共存InnoDB, MyISAM, NDB(MySQL Cluster), Memory, Merge, Archive, CSV, etc
   支持存储过程,触发器和视图
   Replication
   Partitioning
   Information Schema
   MySQL connectors (ODBC, JDBC, .NET, etc)
   MySQL Workbench可视化工具,可用于开发和管理


下图为MySQL架构图


MySQL的存储引擎(比较常用的存储引擎有MyISAM和Innodb)

InnoDB: The default storage engine in MySQL 5.7.InnoDBis a transaction-safe (ACID compliant) storage engine for MySQL that has commit, rollback, and crash-recovery capabilities to protect user data.InnoDBrow-level locking (without escalation to coarser granularity locks) and Oracle-style consistent nonlocking reads increase multi-user concurrency and performance.InnoDBstores user data in clustered indexes to reduce I/O for common queries based on primary keys. To maintain data integrity,InnoDBalso supportsFOREIGN KEYreferential-integrity constraints. For more information aboutInnoDB, see Chapter 15, The InnoDB Storage Engine.事务型数据库的首选引擎,支持ACID事务,支持行级锁定, MySQL 5.5 起成为默认数据库引擎


MyISAM: These tables have a small footprint. Table-level locking limits the performance in read/write workloads, so it is often used in read-only or read-mostly workloads in Web and data warehousing configurations.

MySQL 5.0 之前的默认数据库引擎,最为常用。拥有较高的插入,查询速度,但不支持事务


Memory: Stores all data in RAM, for fast access in environments that require quick lookups of non-critical data. This engine was formerly known as theHEAPengine. Its use cases are decreasing;InnoDBwith its buffer pool memory area provides a general-purpose and durable way to keep most or all data in memory, andNDBCLUSTERprovides fast key-value lookups for huge distributed data sets.所有数据置于内存的存储引擎,拥有极高的插入,更新和查询效率。但是会占用和数据量成正比的内存空间。并且其内容会在 MySQL 重新启动时丢失


CSV: Its tables are really text files with comma-separated values. CSV tables let you import or dump data in CSV format, to exchange data with scripts and applications that read and write that same format. Because CSV tables are not indexed, you typically keep the data inInnoDBtables during normal operation, and only use CSV tables during the import or export stage.逻辑上由逗号分割数据的存储引擎。它会在数据库子目录里为每个数据表创建一个 .csv 文件。这是一种普通文本文件,每个数据行占用一个文本行。CSV 存储引擎不支持索引。


Archive: These compact, unindexed tables are intended for storing and retrieving large amounts of seldom-referenced historical, archived, or security audit information.非常适合存储大量的独立的,作为历史记录的数据。因为它们不经常被读取。Archive 拥有高效的插入速度,但其对查询的支持相对较差


Blackhole: The Blackhole storage engine accepts but does not store data, similar to the Unix/dev/nulldevice. Queries always return an empty set. These tables can be used in replication configurations where DML statements are sent to slave servers, but the master server does not keep its own copy of the data.黑洞引擎,写入的任何数据都会消失,一般用于记录 binlog 做复制的中继


NDB (also known as NDBCLUSTER): This clustered database engine is particularly suited for applications that require the highest possible degree of uptime and availability.高冗余的存储引擎,用多台数据机器联合提供服务以提高整体性能和安全性。适合数据量大,安全和性能要求高的应用


Merge: Enables a MySQL DBA or developer to logically group a series of identicalMyISAMtables and reference them as one object. Good for VLDB environments such as data warehousing.将一定数量的 MyISAM 表联合而成一个整体,在超大规模数据存储时很有用


Federated: Offers the ability to link separate MySQL servers to create one logical database from many physical servers. Very good for distributed or data mart environments.将不同的 MySQL 服务器联合起来,逻辑上组成一个完整的数据库。非常适合分布式应用


Example: This engine serves as an example in the MySQL source code that illustrates how to begin writing new storage engines. It is primarily of interest to developers. The storage engine is a stub that does nothing. You can create tables with this engine, but no data can be stored in them or retrieved from them.她是一个不做任何事情的存根引擎。它的目的是作为 MySQL 源代码中的一个例子,用来演示如何开始编写一个新存储引擎。同样,它的主要兴趣是对开发者。


注意:这些引擎可以共存,在创建表时,可指定引擎(create table <table_name> engine=<engine_name>)。不同的分支引擎的名字也不一样比如percona的XtraDB,MariaDB的PrimeBase XT (PBXT) 等。

存储引擎特性及限制


MyISAM Memory InnoDB Archive NDB
Storage limits 256TB RAM 64TB None 384EB
Transactions No No Yes No Yes
Locking granularity Table Table Row Row Row
MVCC No No Yes No No
Geospatial data type support Yes No Yes Yes Yes
Geospatial indexing support Yes No Yes[a] No No
B-tree indexes Yes Yes Yes No No
T-tree indexes No No No No Yes
Hash indexes No Yes No[b] No Yes
Full-text search indexes Yes No Yes[c] No No
Clustered indexes No No Yes No No
Data caches No N/A Yes No Yes
Index caches Yes N/A Yes No Yes
Compressed data Yes[d] No Yes[e] Yes No
Encrypted data[f] Yes Yes Yes Yes Yes
Cluster database support No No No No Yes
Replication support[g] Yes Yes Yes Yes Yes
Foreign key support No No Yes No No
Backup / point-in-time recovery[h] Yes Yes Yes Yes Yes
Query cache support Yes Yes Yes Yes Yes
Update statistics for data dictionary Yes Yes Yes Yes Yes

[a] InnoDB support for geospatial indexing is available in MySQL 5.7.5 and higher.

[b] InnoDB utilizes hash indexes internally for its Adaptive Hash Index feature.

[c] InnoDB support for FULLTEXT indexes is available in MySQL 5.6.4 and higher.

[d] Compressed MyISAM tables are supported only when using the compressed row format. Tables using the compressed row format with MyISAM are read only.

[e] Compressed InnoDB tables require the InnoDB Barracuda file format.

[f] Implemented in the server (via encryption functions). Data-at-rest tablespace encryption is available in MySQL 5.7 and higher.

[g] Implemented in the server, rather than in the storage engine.

[h] Implemented in the server, rather than in the storage engine.

查看引擎

mysql> show engines;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                                        | Transactions | XA   | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| MyISAM             | YES     | MyISAM storage engine                                          | NO           | NO   | NO         |
| MRG_MYISAM         | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         |
| CSV                | YES     | CSV storage engine                                             | NO           | NO   | NO         |
| BLACKHOLE          | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO   | NO         |
| InnoDB             | DEFAULT | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        |
| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |
| ARCHIVE            | YES     | Archive storage engine                                         | NO           | NO   | NO         |
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                             | NO           | NO   | NO         |
| FEDERATED          | NO      | Federated MySQL storage engine                                 | NULL         | NULL | NULL       |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
9 rows in set (0.00 sec)


mysql> show engines\G
*************************** 1. row ***************************
      Engine: MyISAM
     Support: YES
     Comment: MyISAM storage engine
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 2. row ***************************
      Engine: MRG_MYISAM
     Support: YES
     Comment: Collection of identical MyISAM tables
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 3. row ***************************
      Engine: CSV
     Support: YES
     Comment: CSV storage engine
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 4. row ***************************
      Engine: BLACKHOLE
     Support: YES
     Comment: /dev/null storage engine (anything you write to it disappears)
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 5. row ***************************
      Engine: InnoDB
     Support: DEFAULT
     Comment: Supports transactions, row-level locking, and foreign keys
Transactions: YES
          XA: YES
  Savepoints: YES
*************************** 6. row ***************************
      Engine: MEMORY
     Support: YES
     Comment: Hash based, stored in memory, useful for temporary tables
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 7. row ***************************
      Engine: ARCHIVE
     Support: YES
     Comment: Archive storage engine
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 8. row ***************************
      Engine: PERFORMANCE_SCHEMA
     Support: YES
     Comment: Performance Schema
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 9. row ***************************
      Engine: FEDERATED
     Support: NO
     Comment: Federated MySQL storage engine
Transactions: NULL
          XA: NULL
  Savepoints: NULL
9 rows in set (0.00 sec)
mysql>

如何安装MySQL存储引擎
install plugin <引擎名字> soname '<库名字>';
uninstall plugin <引擎名字>;  #卸载引擎
mysql>install plugin federated soname 'ha_federated.so';
引擎信息
[root@mysql ~]# ls -l /usr/lib64/mysql/plugin/
total 29788
-rwxr-xr-x. 1 root root   100149 Sep 29 01:40 adt_null.so
-rwxr-xr-x. 1 root root    52969 Sep 29 01:41 auth_socket.so
drwxr-xr-x. 2 root root     4096 Nov  8 12:14 debug
-rwxr-xr-x. 1 root root   354505 Sep 29 01:40 ha_example.so
-rwxr-xr-x. 1 root root   693828 Sep 29 01:40 innodb_engine.so
-rwxr-xr-x. 1 root root   565047 Sep 29 01:40 keyring_file.so
-rwxr-xr-x. 1 root root   300431 Sep 29 01:41 keyring_udf.so
-rwxr-xr-x. 1 root root   648990 Sep 29 01:40 libmemcached.so
-rwxr-xr-x. 1 root root  7956928 Sep 29 01:40 libpluginmecab.so
-rwxr-xr-x. 1 root root    16812 Sep 29 01:41 locking_service.so
-rwxr-xr-x. 1 root root    60977 Sep 29 01:40 mypluglib.so
-rwxr-xr-x. 1 root root    50677 Sep 29 01:41 mysql_no_login.so
-rwxr-xr-x. 1 root root 17569269 Sep 29 01:43 mysqlx.so
-rwxr-xr-x. 1 root root    59232 Sep 29 01:40 rewrite_example.so
-rwxr-xr-x. 1 root root   619355 Sep 29 01:40 rewriter.so
-rwxr-xr-x. 1 root root   681740 Sep 29 01:40 semisync_master.so
-rwxr-xr-x. 1 root root   164972 Sep 29 01:40 semisync_slave.so
-rwxr-xr-x. 1 root root   216938 Sep 29 01:40 validate_password.so
-rwxr-xr-x. 1 root root   352014 Sep 29 01:41 version_token.so
[root@mysql ~]#

MyISAM支持以下特性:

   不支持事务
   不支持外键
   支持HASH和BTREE索引
   支持表级别的锁
   读速度很快,适合做data warehouses
   一个表最多有64个索引
   可以通过mysqlhotcopy在线备份
   支持数据压缩(myisampack)
   数据不支持cache
   索引支持cache

MyISAM存储结构:
   结构文件(.frm)
   数据文件(.MYD)
   索引文件(.MYI)

MyISAM常用工具
myisamchk
用于分析,优化,修复表,进而避免数据损坏。做下面的操作,你应当关闭MySQL
# check a table
myisamchk /var/lib/mysql/<table_name>.MYI
# Repair a table
myisamchk -r /var/lib/mysql/<table_name>.MYI

myisampack
用来创建压缩、只读表,做下面的操作,你应当关闭MySQL
# compress a table
myisampack <table_name>.MYI
# Rebuild the indexes for optimal performance
myisampack --rq --sort-index -analyze <table_name>.MYI

myisam_ftdump
用来显示表中的fulltext字段
# First you need the program to analyze, use show create table command
show create table employees\G
# Now you can dump the table, notice the index number at the end,
myisam_ftdump employees 1

MYISAM相关的参数
max_write_lock_count
preload_buffer_size
key_buffer_size        决定index缓存的大小,最小8MB,最大4GB
concurrent_insert      决定并行插入的行为(默认是1)
   0 - disables concurrent inserts
   1 (default) - concurrent inserts with no data gaps are enabled
   2 - concurrent inserts with data gaps are enabled
delay_key_write        对MyISAM表的索引延时更新直到表被关闭(默认是ON)
   ON (default) - MyISAM tables that have delay_key_write option defined will delay index updates
   OFF - disable delayed index writes entirely
   ALL - enable delayed index writes entirely

InnoDB的特性:

InnoDB表由共享表空间、事务日志文件组(ib_logfile)、表结构定义文件组成。表结构文件也以.frm结尾,与存储引擎无关。在InnoDB存储引擎中,默认表空间文件是ibdata1,初始化为10M,且可以扩展。根据参数innodb_file_per_table的设定来决定是否采用共享的表空间,如果该参数设置为OFF。所有的表共享一个数据文件;如果设置为ON,则每个表有自己独立的数据文件。

注意:MySQL可以通过binlog来恢复,这里的ib_logfile是在事务中起前滚或后滚的作用

mysql> show engine innodb status\G
*************************** 1. row ***************************
  Type: InnoDB
  Name:
Status:
=====================================
2016-11-09 10:48:38 0x7f8d4529d700 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 43 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 1 srv_active, 0 srv_shutdown, 1949 srv_idle
srv_master_thread log flush and writes: 1950
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 2
OS WAIT ARRAY INFO: signal count 2
RW-shared spins 0, rounds 4, OS waits 2
RW-excl spins 0, rounds 0, OS waits 0
RW-sx spins 0, rounds 0, OS waits 0
Spin rounds per wait: 4.00 RW-shared, 0.00 RW-excl, 0.00 RW-sx
------------
TRANSACTIONS
------------
Trx id counter 8451
Purge done for trx's n:o < 818 undo n:o < 0 state: running but idle
History list length 6
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 421719879481168, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
--------
FILE I/O
--------
I/O thread 0 state: waiting for completed aio requests (insert buffer thread)
I/O thread 1 state: waiting for completed aio requests (log thread)
I/O thread 2 state: waiting for completed aio requests (read thread)
I/O thread 3 state: waiting for completed aio requests (read thread)
I/O thread 4 state: waiting for completed aio requests (read thread)
I/O thread 5 state: waiting for completed aio requests (read thread)
I/O thread 6 state: waiting for completed aio requests (write thread)
I/O thread 7 state: waiting for completed aio requests (write thread)
I/O thread 8 state: waiting for completed aio requests (write thread)
I/O thread 9 state: waiting for completed aio requests (write thread)
Pending normal aio reads: [0, 0, 0, 0] , aio writes: [0, 0, 0, 0] ,
 ibuf aio reads:, log i/o's:, sync i/o's:
Pending flushes (fsync) log: 0; buffer pool: 0
259 OS file reads, 53 OS file writes, 7 OS fsyncs
0.00 reads/s, 0 avg bytes/read, 0.00 writes/s, 0.00 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 0, seg size 2, 0 merges
merged operations:
 insert 0, delete mark 0, delete 0
discarded operations:
 insert 0, delete mark 0, delete 0
Hash table size 34679, node heap has 0 buffer(s)
Hash table size 34679, node heap has 0 buffer(s)
Hash table size 34679, node heap has 0 buffer(s)
Hash table size 34679, node heap has 0 buffer(s)
Hash table size 34679, node heap has 0 buffer(s)
Hash table size 34679, node heap has 0 buffer(s)
Hash table size 34679, node heap has 0 buffer(s)
Hash table size 34679, node heap has 0 buffer(s)
0.00 hash searches/s, 0.00 non-hash searches/s
---
LOG
---
Log sequence number 2532665
Log flushed up to   2532665
Pages flushed up to 2532665
Last checkpoint at  2532656
0 pending log flushes, 0 pending chkp writes
10 log i/o's done, 0.00 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total large memory allocated 137428992
Dictionary memory allocated 292862
Buffer pool size   8192
Free buffers       7931
Database pages     261
Old database pages 0
Modified db pages  0
Pending reads      0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 227, created 34, written 36
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
No buffer pool page gets since the last printout
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 261, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
0 read views open inside InnoDB
Process ID=9242, Main thread ID=140244602676992, state: sleeping
Number of rows inserted 0, updated 0, deleted 0, read 9
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================
1 row in set (0.00 sec)
mysql>
InnoDB的特点:
   支持事务provided by MVCC (Multi Version Concurrency Control)
   行级锁
   支持外键
   索引使用clustered B-tree indexes
   数据和索引都可以配置buffer cache
   支持在线非阻塞的备份

查看Innodb相关参数

mysql> show variables like '%Innodb_data%';
+-----------------------+------------------------+
| Variable_name         | Value                  |
+-----------------------+------------------------+
| innodb_data_file_path | ibdata1:10M:autoextend |
| innodb_data_home_dir  |                        |
+-----------------------+------------------------+
2 rows in set (0.00 sec)

mysql>
mysql> show variables like '%innodb_log%';
+---------------------------+---------+
| Variable_name             | Value   |
+---------------------------+---------+
| innodb_log_buffer_size    | 1048576 |
| innodb_log_file_size      | 5242880 |
| innodb_log_files_in_group | 2       |
| innodb_log_group_home_dir | ./      |
+---------------------------+---------+
4 rows in set (0.00 sec)

mysql>

Innodb的数据文件和日志文件

[root@mysql mysql]# pwd;ls -ltr ib* /var/lib/mysql
-rw-rw----. 1 mysql mysql  5242880 Nov  2 15:20 ib_logfile1
-rw-rw----. 1 mysql mysql 10485760 Nov  7 16:06 ibdata1
-rw-rw----. 1 mysql mysql  5242880 Nov  7 16:06 ib_logfile0
[root@hdpm mysql]#
如何为Innodb表空间增加数据文件?
innodb_data_file_path=/var/lib/mysqldata/ibdata1:10M:autoextend;/var/lib/mysqldata/ibdata2:100G;/pohs/mysql/data/ibdata3:100M:autoextend 注意:通过设置参数innodb_data_file_path设置该参数,需要停库 

参数innodb_file_per_table

若将innodb_file_per_table设置为on,则系统将为每一个表单独的生成一个table_name.ibd的文件,该文件中存储与该表相关的数据、索引、表的内部数据字典信息。优点:每个表都有自已独立的表空间,每个表的数据和索引都会存在自已的表空间中,可以实现单表在不同的数据库中移动


mysql> show variables like 'innodb_file_per_table';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| innodb_file_per_table | OFF   |
+-----------------------+-------+
1 row in set (0.00 sec) 
mysql>  
mysql> set global innodb_file_per_table =ON; 
ERROR 1238 (HY000): Variable 'innodb_file_per_table' is a read only variable  
mysql> 
需要修改my.cnf/my.ini,然后重启数据库


如何将Innodb共享表空间转化为独立表空间?

1.先逻辑备份,然后修改配置文件my.cnf中的参数innodb_file_per_table参数为1,重启服务后,导入逻辑备份
2.修改配置文件my.cnf中的参数innodb_file_per_table参数为1,重启服务后将需要修改的所有innodb表都执行一遍:
   alter table <table_name> engine=innodb;
注意:新建的表会使用独立表空间

Innodb相关的常用参数

innodb_data_file_path
innodb_data_home_dir	
innodb_file_per_table
innodb_buffer_pool_size
innodb_flush_log_at_trx_commit	
innodb_log_file_size	
innodb_log_files_in_group	
innodb_fast_shutdown	
innodb_flush_method	
innodb_log_buffer_size

如何连接Mysql数据库

mysql命令行参数
-u/--user          指定用户
-p/--password      用户密码
-h/--host          指定host
-S/--socket        当一台服务器上安装了多个mysql,-S用来指定连接到那个
-D/--database      指定数据库名字
-V/--version      获取MySQL版本
-v/--verbose      获取更多输出
   --delimiter    修改默认的分隔符(默认是;)
  --protocol      指定使用哪一种协议
                  Unix - Socket/ TCP
                  Windows - TCP, PIPE/ MEMORY

mysql -u user -ppassword <database> -e "select * from mysql"
mysql -u root -pohsdba test -e "select * from a"
[root@hdpm ~]#  mysql -u root -pohsdba test -e "select * from a\G"
*************************** 1. row ***************************
a: 10
[root@hdpm ~]#  mysql -u root -pohsdba test -e "show create table a\G"
*************************** 1. row ***************************
       Table: a
Create Table: CREATE TABLE `a` (
  `a` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
[root@hdpm ~]#

mysql -u user -ppassword <database> < sql_commands.sql
注意:-ppassword中间没有空格

MySQL常用命令

mysql> \u mysql
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> use mysql
Database changed
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)
mysql>
mysql> select user();
+----------------+
| user()         |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.00 sec)
mysql> select database();
+------------+
| database() |
+------------+
| mysql       |
+------------+
1 row in set (0.00 sec)
mysql> select @@license;
+-----------+
| @@license |
+-----------+
| GPL       |
+-----------+
1 row in set (0.00 sec)
mysql>
mysql> \! date   #执行shell命令
Mon Nov  7 23:47:23 CST 2016
mysql>

启动关闭MySQL

[root@mysql ~]# /etc/init.d/mysqld
Usage: /etc/init.d/mysqld {start|stop|status|restart|condrestart|try-restart|reload|force-reload}
[root@hdpm ~]# /etc/init.d/mysqld status
mysqld (pid  13224) is running...
[root@hdpm ~]#

service mysql stop
mysqladmin shutdown -uroot -p 
mysqladmin命令

Where command is a one or more of: (Commands may be shortened)
  create databasename   Create a new database
  debug                 Instruct server to write debug information to log
  drop databasename     Delete a database and all its tables
  extended-status       Gives an extended status message from the server
  flush-hosts           Flush all cached hosts
  flush-logs            Flush all logs
  flush-status          Clear status variables
  flush-tables          Flush all tables
  flush-threads         Flush the thread cache
  flush-privileges      Reload grant tables (same as reload)
  kill id,id,...        Kill mysql threads
  password new-password Change old password to new-password, MySQL 4.1 hashing.
  old-password new-password Change old password to new-password in old format.

  ping                  Check if mysqld is alive
  processlist           Show list of active threads in server
  reload                Reload grant tables
  refresh               Flush all tables and close and open logfiles
  shutdown              Take server down
  status                Gives a short status message from the server
  start-slave           Start slave
  stop-slave            Stop slave
  variables             Prints variables available
  version               Get version info from server


Reference

http://dba.stackexchange.com/questions/9139/how-to-add-federated-engine-after-installing-mysql

http://dev.mysql.com/doc/refman/5.7/en/storage-engines.html

http://www.codeproject.com/Articles/1107279/Writing-a-MySQL-storage-engine-from-scratch



关键词:mysql 

相关文章

MySQL and Oracle Enterprise Manager
MySQL Tools for performance, backup, monitor
MySQL Multi-Master Single-Slave Replication
MySQL and GPL
MySQL monthly report from alibaba
MySQL备份
MySQL配置文件my.cnf
MySQL数据类型
MySQL字符集
MySQL入门
MySQL的基本架构
关于MySQL,你需要了解的

联系我们

韩朝阳



    Phone:186-9589-1286

    E-mail:ohsdba@qq.com

    微  信: ohsdba


电话(186-9589-1286)

QQ(375349564)

微信(ohsdba)


常用链接

Oracle
eDelivery
My Oracle Support

Exadata 12.1 Document

Exadata 12.2 Document

Oracle 11gR2 Document

Oracle 12cR1 Document

Oracle 12cR2 Document

GoldenGate 12c

Oracle Fusion Middleware

Mysql Document

PostgreSQL

Python

github

sourceforge



Top
Cell186-9589-1286
QQ 375349564
E-mail ohsdba@qq.com