MySQL数据库运维管理

1、技术创新，变革未来MySQL数据库运维管理MySQL数据库运维管理04MySQL运维管理参数文件位置：直接运行可执行文件mysqld：rootmaster # which mysqldrootmaster # ps -ef|grep mysqld|grep -v greprootmaster # /mysql/app/bin/mysqld -verbose -help|grep -A 1 Default optionsDefault options are read from the following files in the given order:/etc/f /etc/mysql/f

2、/usr/local/mysql/etc/f /.fmysql5.6:# cp $BASEDIR/support-files/f /etc/f # Vi /etc/fmysql5.7:# Vi /etc/f04MySQL运维管理编辑初始化参数文件fclient port = socket = user = port =.mysql auto-rehashprompt = uh R:m:s d.mysqlduser = port = basedir = datadir =.innodb.quick max_allowed_packet =./my-cnf-wizard.html只供参考学习！04

3、MySQL运维管理创建服务方式启动（ SHELL脚本）：cp $BASEDIR/support-files/mysql.server /etc/init.d/mysql授予执行权限、配置MySQL服务开机自启动启动MySQL服务service mysql start、systemctl start mysql以主进程mysqld方式启动：$BASEDIR/bin/mysqld defaults-file=/etc/f &04MySQL运维管理以守护进程mysqld_safe（ SHELL脚本）方式启动：$BASEDIR/bin/mysqld_safe defaults-file=/etc/f

4、&以多实例进程mysqld_multi（SHELL脚本）方式启动：$BASEDIR/bin/mysqld_multi defaults-file=/etc/f &04MySQL运维管理检查监听端口：修改MySQL系统账户rootlocalhost密码：Mysql5.6:/usr/local/mysql/mysqladmin -u root password mysql S /tmp/mysql3306.sockMysql5.7:/usr/local/mysql/mysqladmin -u root password /ai+&+HhX8kw S /tmp/mysql3307.sock04MyS

5、QL运维管理数据库登录（连接）：rootMYSQL01 # mysql -uroot -pmysqlmysql: Warning Using a password on the command line interface can be insecure. Welcome to the MySQL monitor. Commands end with ; or g.Your MySQL connection id is 16425Server version: 5.7.21-log MySQL Community Server (GPL)Copyright (c) 2000, 2018, Or

6、acle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners.Type help; or h for help. Type c to clear the current input statement. mysql show global variables like port;+ + +| V

7、ariable_name | Value |+ + +| port| 3306 |+ + +1 row in set (0.00 sec)04MySQL运维管理Socket方式连接mysqlVAGE01 $ mysql -usystem -pOracle1 -Dvage -S /data/mysqldata/mysql1/mysql.sock Warning: Using a password on the command line interface can be insecure.Welcome to the MySQL monitor.Commands end with ; or g.

8、Your MySQL connection id is 4Type help; or h for help. Type c to clear the current input statement. mysql-u：用户名-p：密码-D：数据库名-s：Socket套接字文件此种方式只能用于本地连接。Socket文件被强制干掉 了怎么办04MySQL运维管理IP方式连接mysqlVAGE01 $ mysql -usystem -pOracle1 -hlocalhost -P3306 -Dvage Warning: Using a password on the command line inte

9、rface can be insecure. Welcome to the MySQL monitor.Commands end with ; or g.Your MySQL connection id is 4Type help; or h for help. Type c to clear the current input statement. mysql-u：用户名-p：密码-h：IP或主机名-P：端口号，默认3306-D：数据库名04MySQL运维管理客户端工具MySQLsqlplus：不仅仅是一个连接Oracle的客户端，还是一个管理工具，创建、删除数据库，启动、关闭等操作 都可以

10、在sqlplus中完成。mysql：连接mysql的客户端，无法完成整库级的创建、删除、启动、关闭等基础管理操作。04MySQL运维管理数据库账户授权表mysql.user数据库账户权限合理管控DDL:grant create/drop/alter XXX on XXX.XXX to userhost with grant option;DML:grant select,insert,update,delete on XXX.XXX to userhost with grant option;只读:grant select on XXX.XXX to userhost with grant o

11、ption;04MySQL运维管理多种方式修改账户密码：rootMYSQL01 # mysql -uroot -pmysql mysql select user();rootlocalhostmysql set password=mysql123;mysql alter user rootlocalhost identified by mysql123;mysql update mysql.user set authentication_string=password(mysql123) where user=root and host=localhost;mysql flush privil

12、eges;Query OK, 0 rows affected (0.02 sec) mysql exitByerootMYSQL01 # mysql -uroot -pmysqlERROR 1045 (28000): Access denied for user rootlocalhost (using password: YES)04MySQL运维管理系统账户root初始密码不知怎么办04MySQL运维管理停止MySQL方式：# mysqladmin -uroot -p -S /tmp/mysql5721_mslave.sock shutdown# service mysql stop 或是

13、 systemctl stop mysql (/etc/init.d/mysql stop)# pkill mysqld_safe & pkill mysqldMysqladmin是一个可执行文件，是MySQL的重要工具，除关闭数据库，还可以完成创建数据库、查 看线程运行状态、修改root密码、杀线程、加载权限表及启停slave服务等重要操作。04MySQL运维管理数据库介绍：mysql show databases;+ +| Database|+ +|information_schema|mysql|performance_schema|sys|tpcc1000|+ +information

14、_schema存储着数据库中所有元数据信息，即数据字典信息，是一个只读库，只支 持select操作，介绍下常用表：TablesPartitionsGlobal_statusGlobal_variables记录所有表的基本信息记录数据库表分区情况记录MySQL服务运行各状态值记录数据库各参数配置04MySQL运维管理数据库介绍：INNODB_LOCKS、INNODB_LOCK_WAITS、INNODB_TRX监控数据库事务锁情况mysql库下存储着数据库账户权限认证信息，各数据库对象各级权限信息，及表和索引对象统计 信息收集情况等Userinnodb_index_statsinnodb_tabl

15、e_stats用于管理数据库账户权限记录事务引擎innodb索引对象统计信息记录事务引擎innodb表对象统计信息04MySQL运维管理存储引擎：主流存储引擎：Innodb：最相似Oracle的存储引擎，支持事务，有UNDO、Redo机制，可以保证数据毫不丢失，细 粒度的锁机制（行级锁），等等。MyISAM：MySQL 5.5前默认的存储引擎，没有事务，并发控制简单（但锁粒度较粗，表级锁），它的特点就是简单、快速。常用存储引擎：CSV存储引擎：以普通平面文件做为表文件，提供类似Oracle外部表的功能，常用于导入/出数据。MEMORY存储引擎：内存表，提供类似Redis或MemCached的功

16、能。 III.NDB存储引擎：集群存储引擎(Sharing nothing)，提供类似RAC的架构。IV. TokuDB存储引擎：据说速度很快的存储引擎(目前，阿里巴巴在用的)。选择建议：选择成熟度高的引擎04MySQL运维管理存储引擎使用：使用Innodb：mysql Create table mc1(id int , name varchar(40), bir date, primary key(id,bir) ) engine=innodb;Query OK, 0 rows affected (0.06 sec)使用MyISAM：mysql Create table mc2(id int

17、 , name varchar(40), bir date, primary key(id,bir) ) engine=myisam;Query OK, 0 rows affected (0.01 sec)04MySQL运维管理与Oracle横向比较名词ORACLEMYSQL运行方式多进程单进程：Mysqld，多线程实例进程+内存Mysqld进程+内存表空间表空间+数据文件单表一个文件，或所有表在一个文件中， 每一个表对应的文件称为一个表空间数据库全部文件的整体本质上是一个目录，类似Oracle的表空间或Schema的概念。存储引擎无定义数据存储格式和数据读写方式，扩展MySQL功能。04My

18、SQL的表空间与数据库数据库不同于Oracle，MySQL数据库其实就是一个文件系统中的目录，类似于Oracle中的表空间的概念，将表分类 存储在不同地方。mysql create database hzmc;Query OK, 1 row affected (0.00 sec)mysql show variables like datadir%;+ + +| Variable_name | Value|+ + +| datadir| /data/mysqldata/mysql1/data/ |+ + +1 row in set (0.00 sec)roothzmc # cd /data/my

19、sqldata/mysql1/data/ roothzmc data# ls -lFrt04MySQL的表空间与数据库数据库不同于Oracle，MySQL数据库其实就是一个文件系统中的目录，类似于Oracle中的表空间的概念，将表分类 存储在不同地方。roothzmc # cd /data/mysqldata/mysql1/data/ roothzmc data# ls -lFrttotal 16drwx-.2mysqlmysql4096Mar192016performance_schema/drwx-.2mysqlmysql4096Mar192016mysql/-rw-rw-.1mysqlm

20、ysql56Mar192016fdrwx-.2mysqlmysql32May12016test/drwx-.2mysqlmysql4096Feb2410:37/drwx-.2mysqlmysql19Feb2415:37hzmc/roothzmc data#04MySQL的表空间与数据库数据库手动mkdir创建目录，也算是创建数据库：roothzmc data# mkdir abcde roothzmc data#mysql show databases;+ +| Database|+ +|information_schema|abcde|hzmc|mysql|performance_schem

21、a|test|+ +7 rows in set (0.00 sec)04MySQL的表空间与数据库表空间与数据文件所有表在一批操作系统文件中（ innodb_file_per_table 参数值为OFF），不推荐此方式。 设置参数innodb_file_per_table为“ON” ，一个用户表对应两个操作系统文件。在hzmc数据库中创建表T1：mysql use hzmc Database changed mysqlmysql Create table t1(id int , name varchar(40), bir date, primary key(id,bir) ) engine=i

22、nnodb;Query OK, 0 rows affected (0.01 sec)04MySQL的表空间与数据库表空间与数据文件roothzmc data# pwd/data/mysqldata/mysql1/data roothzmc data# cd hzmc- 当前位置为 datadir 参数对应目录- 进入hzmc数据库对应的目录roothzmc hzmc# ls -lFrt total 112-rw-rw-.1mysqlmysql61Feb2415:37db.opt-rw-rw-.1mysqlmysql8614Feb2415:45t1.frm-rw-rw-.1mysqlmysql9

23、8304Feb2415:45t1.ibdroothzmc hzmc#t1.frm : frm文件，元数据文件，保存表结构等元数据。t1.ibd : ibd文件，表数据所在文件，可以称为t1表的表空间文件。另需注意，MySQL中没有数据文件的 叫法。MySQL的表空间与数据文件：MySQL没有数据文件的叫法，每个表对应一个表空间文件（ innodb_file_per_table为ON），数据就在表空间文件中。表大小受文件系统单一文件大小限制。04基本操作与命令参照对比名词ORACLEMYSQLSelect无太大差别。显示计算 结果使用dual表:Select 2*3 from dual无太大差别

24、，显示计算结果不需使用dual表: Select 2*3 ；DML：Insert UpdateDelete无太大差别无太大差别Create table主体语法、主键、大部 分约束语法相同于 MySQL增加了一些MySQL独特的特性，比如： “engine=innodb”这样的。显示参数值Show parameter show variables like %;设置参数值Alter system/session set XXX=yyyset read_buffer_size=524288;set global.read_buffer_size=524288;数据库04基本操作与命令参照对比名词ORACLEMYSQL创建表空间有无此命令创建数据库命令选项复杂Create database XXX，相当于直接