前言: 本文旨在总结mysql数据库运维的常见操作，包括如下内容:
一>登录mysql数据库(help 命令,例如:help grant)
1>单实例登录:mysql -u root -p
二>修改mysql数据库管理员密码
法一:用mysqladmin更改
A>单实例:
mysqladmin -u root -p '123456' password '1234567'
B>多实例:
mysqladmin -u root -p '123456' password '1234567' -S /data/3306/mysql.sock
法二:用sql语句更改(此法适合密码丢失后通过--skip-grant-tables参数启动数据库后修改密码)
例如:忘记mysql的root密码时候:
[root@lll3 ssh]# service mysqld stop --------------先停止mysql
Shutting down MySQL. SUCCESS!
[root@lll3 ssh]# vim /etc/my.cnf -----------在/etc/my.cnf的mysqld模块中加入--skip-grant-tables,重启即可免密码登录mysql
# Example MySQL config file for small systems.
#
# This is for a system with little memory (<= 64M) where MySQL is only used
# from time to time and it's important that the mysqld daemon
# doesn't use much resources.
#
# MySQL programs look for option files in a set of
# locations which depend on the deployment platform.
# You can copy this option file to one of those
# locations. For information about these locations, see:
# http://dev.mysql.com/doc/mysql/en/option-files.html
#
# In this file, you can use all long options that a program supports.
# If you want to know which options a program supports, run the program
# with the "--help" option.
# The following options will be passed to all MySQL clients
[client]
#password = your_password
port = 3306
socket = /application/mysql-5.5.32/tmp/mysql.sock
# Here follows entries for some specific programs
# The MySQL server
[mysqld]
skip-grant-tables ------新加入skip-grant-tables
port = 3306
socket = /application/mysql-5.5.32/tmp/mysql.sock
[root@lll3 ssh]# service mysqld start -----重启mysql
Starting MySQL... SUCCESS!
[root@lll3 ssh]# mysql -u root -p
Enter password: -----此时随意输入密码都能登录
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.5.32 Source distribution
Copyright (c) 2000, 2013, Oracle 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> update mysql.user set password=PASSWORD('123456') where user='root'; ------修改mysql的mysql库下面的user表的root密码，
------MySQL5.7下的修改字段为authentication_string
Query OK, 3 rows affected (0.01 sec)
Rows matched: 5 Changed: 3 Warnings: 0
然后退出将/etc/my.cnf中的skip-grant-tables去掉重启mysql用修改后 的密码登录即可
法三:用set语句修改
set password=password("123456");
三>创建不同字符集数据库
1>创建默认字符集数据库
mysql> create database oldboy;-------创建名为oldboy的数据库
Query OK, 1 row affected (0.00 sec)
mysql> show create database oldboy\G -----查看oldboy库的建库语句,默认是utf-8的字符集,因为编译安装mysql时指定了utf-8
*************************** 1. row ***************************
Database: oldboy
Create Database: CREATE DATABASE `oldboy` /*!40100 DEFAULT CHARACTER SET utf8 */
1 row in set (0.01 sec)2>创建字符集为gbk的数据库
mysql> create database oldboy_gbk DEFAULT CHARACTER SET gbk COLLATE gbk_chinese_ci;
Query OK, 1 row affected (0.07 sec)
mysql> show create database oldboy_gbk\G
*************************** 1. row ***************************
Database: oldboy_gbk
Create Database: CREATE DATABASE `oldboy_gbk` /*!40100 DEFAULT CHARACTER SET gbk */
1 row in set (0.01 sec)总结:企业里怎么创建数据库？
1>根据开发的程序确定字符集（建议utf-8）
2>编译安装数据库的时候指定字符集,例如:DEFAULT_CHARSET=utf8
DEFAULT_COLLATION=utf8_general_ci\
然后建库的时候默认创建即可,即create database oldboy
3>编译安装的时候没有指定字符集或者指定了和程序不同的字符集,指定字符集创建数据库即可
create database oldboy_gbk DEFAULT CHARACTER SET gbk COLLATE gbk_chinese_ci;----创建gbk字符集数据库
create database oldboy_utf8 DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;----创建utf8字符集数据库
当然，数据库要支持创建数据库的字符集。
四>显示mysql数据库
mysql> show databases;----显示所有数据库
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| oldboy |
| oldboy_gbk |
| performance_schema |
| test |
+--------------------+
6 rows in set (0.00 sec)
mysql> show databases like 'old%';----模糊查询显示数据库
+-----------------+
| Database (old%) |
+-----------------+
| oldboy |
| oldboy_gbk |
+-----------------+
2 rows in set (0.06 sec)
mysql> select database();----显示当前数据库
+------------+
| database() |
+------------+
| oldboy |
+------------+
1 row in set (0.00 sec)五>删除mysql数据库
mysql> drop database test;
Query OK, 0 rows affected (0.26 sec)六>连接mysql数据库
mysql> use oldboy;
Database changed
mysql> select user();---查看用户
+----------------+
| user() |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.07 sec)
mysql> select now();---查看时间
+---------------------+
| now() |
+---------------------+
| 2018-09-11 21:18:30 |
+---------------------+
1 row in set (0.06 sec)
mysql> select version();---查看数据库版本
+-----------+
| version() |
+-----------+
| 5.5.32 |
+-----------+
1 row in set (0.07 sec)七>查询当前的数据库包含的表的信息
mysql> use oldboy;----连接oldboy库
Database changed
mysql> show tables;
Empty set (0.06 sec)八>创建mysql用户及赋予用户权限
创建oldboy用户，对oldboy库具备所有权限,允许从localhost主机登录管理数据库，密码是oldboy123
mysql> grant all privileges on oldboy.* to 'oldboy'@'localhost' identified by 'oldboy123';--创建用户并赋予权限
Query OK, 0 rows affected (0.09 sec)
mysql> flush privileges;-----权限生效
Query OK, 0 rows affected (0.07 sec)
mysql> show grants for oldboy@localhost;----显示oldboy权限
+---------------------------------------------------------------------------------------------------------------+
| Grants for oldboy@localhost |
+---------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'oldboy'@'localhost' IDENTIFIED BY PASSWORD '*FE28814B4A8B3309DAC6ED7D3237ADED6DA1E515' |
| GRANT ALL PRIVILEGES ON `oldboy`.* TO 'oldboy'@'localhost' |
+---------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)九>授权局域网内主机远程连接数据库
根据grant命令语法,我们知道test@'localhost'位置为授权访问数据库的主机,localhost可以用域名,IP地址或者ip段来替代
百分号匹配法:
mysql> create user oldgirl@'192.168.0.%' identified by '123456';
Query OK, 0 rows affected (0.01 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)
网段授权:
mysql> create user oldgirl1@'192.168.0.0/255.255.255.0' identified by '123456';----创建oldgril1用户允许从192.168.0.网段连接
Query OK, 0 rows affected (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)十>确定mysql的授权授权列表
mysql> show grants for oldboy@localhost;
+---------------------------------------------------------------------------------------------------------------+
| Grants for oldboy@localhost |
+---------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'oldboy'@'localhost' IDENTIFIED BY PASSWORD '*FE28814B4A8B3309DAC6ED7D3237ADED6DA1E515' |
| GRANT ALL PRIVILEGES ON `oldboy`.* TO 'oldboy'@'localhost' |
+---------------------------------------------------------------------------------------------------------------+
2 rows in set (0.06 sec)
mysql> REVOKE INSERT ON oldboy.* FROM 'oldboy'@'localhost'; ----收回insert权限
Query OK, 0 rows affected (0.08 sec)
mysql> show grants for oldboy@localhost;
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Grants for oldboy@localhost |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'oldboy'@'localhost' IDENTIFIED BY PASSWORD '*FE28814B4A8B3309DAC6ED7D3237ADED6DA1E515' |
| GRANT SELECT, UPDATE, DELETE, CREATE, DROP, REFERENCES, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, EVENT, TRIGGER ON `oldboy`.* TO 'oldboy'@'localhost' |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)十一>mysql创建表
DROP TABLE IF EXISTS `bm_jgbm`;
CREATE TABLE `bm_jgbm` (
`bm` varchar(20) NOT NULL,
`mc` varchar(100) DEFAULT NULL,
`version` int(10) DEFAULT '1',
PRIMARY KEY (`bm`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;十二>mysql查看表结构
mysql> desc user; -----查看表结构
+------------------------+-----------------------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------------------+-----------------------------------+------+-----+---------+-------+
| Host | char(60) | NO | PRI | | |
| User | char(16) | NO | PRI | | |
| Password | char(41) | NO | | | |
| Select_priv | enum('N','Y') | NO | | N | |
| Insert_priv | enum('N','Y') | NO | | N | |
| Update_priv | enum('N','Y') | NO | | N | |
| Delete_priv | enum('N','Y') | NO | | N | |
| Create_priv | enum('N','Y') | NO | | N | |
| Drop_priv | enum('N','Y') | NO | | N | |
| Reload_priv | enum('N','Y') | NO | | N | |
| Shutdown_priv | enum('N','Y') | NO | | N | |
| Process_priv | enum('N','Y') | NO | | N | |
| File_priv | enum('N','Y') | NO | | N | |
| Grant_priv | enum('N','Y') | NO | | N | |
| References_priv | enum('N','Y') | NO | | N | |
| Index_priv | enum('N','Y') | NO | | N | |
| Alter_priv | enum('N','Y') | NO | | N | |
| Show_db_priv | enum('N','Y') | NO | | N | |
| Super_priv | enum('N','Y') | NO | | N | |
| Create_tmp_table_priv | enum('N','Y') | NO | | N | |
| Lock_tables_priv | enum('N','Y') | NO | | N | |
| Execute_priv | enum('N','Y') | NO | | N | |
| Repl_slave_priv | enum('N','Y') | NO | | N | |
| Repl_client_priv | enum('N','Y') | NO | | N | |
| Create_view_priv | enum('N','Y') | NO | | N | |
| Show_view_priv | enum('N','Y') | NO | | N | |
| Create_routine_priv | enum('N','Y') | NO | | N | |
| Alter_routine_priv | enum('N','Y') | NO | | N | |
| Create_user_priv | enum('N','Y') | NO | | N | |
| Event_priv | enum('N','Y') | NO | | N | |
| Trigger_priv | enum('N','Y') | NO | | N | |
| Create_tablespace_priv | enum('N','Y') | NO | | N | |
| ssl_type | enum('','ANY','X509','SPECIFIED') | NO | | | |
| ssl_cipher | blob | NO | | NULL | |
| x509_issuer | blob | NO | | NULL | |
| x509_subject | blob | NO | | NULL | |
| max_questions | int(11) unsigned | NO | | 0 | |
| max_updates | int(11) unsigned | NO | | 0 | |
| max_connections | int(11) unsigned | NO | | 0 | |
| max_user_connections | int(11) unsigned | NO | | 0 | |
| plugin | char(64) | YES | | | |
| authentication_string | text | YES | | NULL | |
+------------------------+-----------------------------------+------+-----+---------+-------+
42 rows in set (0.07 sec)十三>mysql创建索引
DROP TABLE IF EXISTS `t_sy_institution`;
CREATE TABLE `t_sy_institution` (
`id` int(20) NOT NULL DEFAULT '0',
`parentid` int(20) DEFAULT NULL,
`name` varchar(100) DEFAULT NULL,
`s_state` int(4) DEFAULT NULL,
`zjbzxbm` varchar(20) DEFAULT NULL COMMENT '在住建部制定的编码文档编码表中对应的机构编码',
`jglb` varchar(4) DEFAULT '01',
PRIMARY KEY (`id`),
UNIQUE KEY `zjbzxbm_index` (`zjbzxbm`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;总结:
1>在表的单列或多列上创建索引（建立在表的字段上的一种快速的地址对应关系），可以加快查询速度，分为主键索引，普通索引。索引占用系统空间（空间），更新数据库时需要维护索引（时间），
索引并不是越多越好。索引一定要创建在where后的条件列,数十到几百行的小表上无需建立索引，更新频繁，读取少的表上要少建立索引。尽量选择在唯一值多的大表的字段上建立索引。
2>创建索引相关命令集合:
创建主键索引
alter table student change id id int primary key auto_increment;
删除主键索引
alter table student drop primary key
创建普通索引
alter table student add index index_dept(dept(8))
根据列的前n个字符创建索引
create index index_dept on student(dept(8))
根据多个列创建联合索引
create index ind_name_dept on student(name,dept);
根据多个列的前n个字符创建索引
create index ind_name_dept on student(name(8),dept(10));
删除普通索引
Alter table student drop index index_dept
drop index ind_name_dept on student
创建唯一索引
create unique index uni_ind_name on student(name);
十四>DML语句之insert
1>建立test表
mysql> CREATE TABLE test(
-> id int(4) NOT NULL AUTO_INCREMENT,
-> name char(20) NOT NULL,
-> PRIMARY KEY (id)
-> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.22 sec)2>按规矩指定所有列名，并且每列都插入值
mysql> insert into test(id,name) value(1,"oldboy");
Query OK, 1 row affected (0.32 sec)3>由于id列为自增的,所以只在name列插入值
mysql> insert into test(name) value("oldboy");
Query OK, 1 row affected (0.07 sec)
4>如果不指定列，就要按规矩为每列都插入适当的值
mysql> insert into test values(3,'oldboy');
Query OK, 1 row affected (0.01 sec)5>批量插入数据方法提升效率。
mysql> insert into test values(4,'woshishei'),(5,'nimei');
Query OK, 2 rows affected (0.01 sec)
Records: 2 Duplicates: 0 Warnings: 06>数据插入成功，然后备份上述数据，留着备用。备份后检查备份的sql数据内容；过滤无用信息。
msyqldump是导出sql语句的形式备份。
mysqldump -uroot -p123456 -B mysql >/opt/mysql_bak.sql
十五>mysql用explain去查看执行计划(有没有走索引)
mysql> select * from test;
+----+-----------+
| id | name |
+----+-----------+
| 1 | oldboy |
| 2 | oldboy |
| 3 | oldboy |
| 4 | woshishei |
| 5 | nimei |
+----+-----------+
5 rows in set (0.07 sec)mysql> explain select * from test where name='oldboy'\G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: test
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 5
Extra: Using where
1 row in set (0.01 sec)
ERROR:
No query specified
mysql> create index index_name on test(name);
Query OK, 0 rows affected (0.22 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc test;
+-------+----------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+----------------+
| id | int(4) | NO | PRI | NULL | auto_increment |
| name | char(20) | NO | MUL | NULL | |
+-------+----------+------+-----+---------+----------------+
2 rows in set (0.01 sec)
mysql> explain select * from test where name='oldboy'\G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: test
type: ref
possible_keys: index_name
key: index_name
key_len: 60
ref: const
rows: 3
Extra: Using where; Using index
1 row in set (0.01 sec)
ERROR:
No query specified十六>清除表中的数据(谨慎操作)
mysql> select * from test;
+----+-----------+
| id | name |
+----+-----------+
| 1 | oldboy |
| 2 | oldboy |
| 3 | oldboy |
| 4 | woshishei |
| 5 | nimei |
+----+-----------+
5 rows in set (0.00 sec)
mysql> truncate table test;
Query OK, 0 rows affected (0.07 sec)
mysql> desc test;------证明truncate只是清除了表数据，没有清除表结构
+-------+----------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+----------------+
| id | int(4) | NO | PRI | NULL | auto_increment |
| name | char(20) | NO | | NULL | |
+-------+----------+------+-----+---------+----------------+
2 rows in set (0.01 sec)十七>增删改表的字段
mysql> alter table test add sex char(4); -----增加字段
Query OK, 7 rows affected (0.11 sec)
Records: 7 Duplicates: 0 Warnings: 0
mysql> alter table test add age int(4) after name;---增加字段在某个字段后面
Query OK, 7 rows affected (0.14 sec)
Records: 7 Duplicates: 0 Warnings: 0
mysql> alter table test add qq varchar(10) first;-----增加字段在第一行
Query OK, 7 rows affected (0.05 sec)
Records: 7 Duplicates: 0 Warnings: 0
十八>更改表名及删除表（谨慎操作）
mysql> rename table test1 to test; ----将表名由test1改为test
Query OK, 0 rows affected (0.01 sec)
mysql> alter table test1 rename to test;---将表名由test1改为test
Query OK, 0 rows affected (0.01 sec)
mysql> drop table test; -----删除表test
Query OK, 0 rows affected (0.07 sec)
mysql> desc test; ------表结构和数据都没有了，表已不存在
ERROR 1146 (42S02): Table 'mysql.test' doesn't exist十九>Linux下MySQL的平滑启动，停止与重启
一、启动方式
1、使用 service 启动：service mysqld start
2、使用 mysqld 脚本启动：/etc/inint.d/mysqld start
二、停止
1、使用 service 启动：service mysqld stop
2、使用 mysqld 脚本启动：/etc/inint.d/mysqld stop
三、重启
1、使用 service 启动：service mysqld restart
2、使用 mysqld 脚本启动：/etc/inint.d/mysqld restart
注意：尽量不要使用kill的方式停止mysql,因为这种野蛮的方法会强行终止MySQL数据库服务，有可能导致表损坏
转载于:https://blog.51cto.com/11218855/2270401
985
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
