首页 > 代码库 > mysql常用命令
mysql常用命令
mysql
文件系统管理数据存在的问题
1、数据冗余性和不一致性
2、数据访问
3、数据相对独立
4、完整性和原子性问题
5、并发访问异常
6、安全性问题
数据库：指的是以一定方式储存在一起，能为多个用户共享，具有尽可能小的冗余度的特点，是与应用程序彼此独立的数据集合。
1、实现数据共享
2、减少数据的冗余度
3、数据实现集中控制
4、数据的一致性
5、故障恢复
关系式数据库
DBMS 数据库管理系统
软件、管理数据库
层次模型 网状模型 关系模型
RDBMS 关系数据库管理系统 DBMS = RDBMS
E-R模型
ERM概念 实体关系建模
实体：实体就是指的数据对象
联系：联系代表一个或多个实体之间的联系
属性：实体的某一特性
超市： 超市名 超市地址
商品： 商品名称 商品价格
员工： 性名 性别 编号
销售： 商品 超市 销售量
创建数据库 修改数据库
SQL语句 结构化查询语言
大型共享数据库数据的关系模型
SQL发展历史
System R DB2 SQL-SERVER T-SQL
ORACLE PL/SQL ANSI
SQL 分类
数据操作语言DML:用于操作数据库对象中所包含的数据。
例如|：INSERT, UPDATE, DELETE语句。
数据定义语言DDL: 用于定义和管理数据对象，包括数据库，数据表等。
例如：CREATE, DROP删除 ALTER修改数据表等。
数据查询语言DQL：用于查询数据库对象中所包含的数据。
例如： SELECT
数据控制语言DCL：是用来管理数据库的语言，包括管理权限及数据更改。
例如： GRANT授权 REVOKE删除权限
关系结构
文件系统的逻辑关系
上层：文件系统中的数据，体现成文件的形式。
底层：数据存在硬盘的数据块
中间层：文件系统
数据库的逻辑关系
上层：数据表
底层：文件
中间层：能够将文件逻辑成表的组件，存储引擎
存储引擎
MyISAM 特点：最常见，查询速度较快 缺点：不支持事务，行级锁和外键约束功能
事务：一段SQL语句的批处理，具有原子性（要么全部SQL语句都执行成功，要么全部SQL语句都执行不成功）
锁
行级锁：InnoDB数据不能修改行级锁定的数据，其它的可以修改
表级锁：MyISAM 直接锁定整张表，要么都能修，要么都不能修改
页级锁：DBD 锁定一张表格的相邻的几组数据
约束：
域约束：某个单元格填写某种类型的数据
外建约束：约束数据正确性
InnoDB
Memory基本内存的引擎，速度快 缺点：不能永远保存数据
数据库
1、只支持一种存储引擎 ORACLE
2、支持多种存储引擎 MYSQL
数据库结构分为两大类
存储管理器
权限及完整性管理器
事务管理器
文件管理器
缓冲区管理器
查询管理器
DDL DML
查询执行引擎
数据库工作模式
单进程多线程模式
守护线程
应用线程
优化数据库性能
缓存
线程重用
硬件
SMP 对称多处理器结构
MYSQL 版本
社区版 企业版 集群版
[root@localhost ~]# mysql -u root -p123456 -h localhost -P 3306 #登陆mysql
mysql>create user you2@‘localhost‘ identified by ‘123‘; #设置普通本地用户 you 123456
mysql>create user you@’%’ identified by ‘123456’ #设置普通用户 you 123456 %代表任意远端地址登陆，在本地没办法登陆
mysql>set password=password(‘123456‘); #更改root管理员的密码123456
mysql>set password for ‘you‘@‘localhost‘=password(‘123‘); 更改普通用户密码为123
mysql>drop user ppp@‘%‘; 删除用户
mysql>drop user root@‘localhost‘;
mysql>select user , host from mysql . user; 查看mysql的用户信息
单实例
如何找回密码：
1、停止数据库
2、修改主配件文件 vim /etc/my.cnf 加入 （skip-grant-tables 跳过授权表）
3、mysql> update mysql.user set password=password(‘123‘) where user=‘root‘; 修改密码
4、exit 数据库
5、删除授权操作skip-grant-tables
6、service mysqld restart 重启并用新密码测试登录
登陆 mysql -uroot -p
多实例修改密码
1、停止数据库
2、修改主配件文件 vim /etc/my.cnf 加入 （skip-grant-tables 跳过授权表）
3、mysql> update mysql.user set password=password(‘123‘) where user=‘root‘; 修改密码
4、exit 数据库
5、删除授权操作skip-grant-tables
6、service mysqld restart 重启并用新密码测试登录
登陆mysql -u root -p -S /data/3306/mysql.sock
mysql> select user,host from mysql.user; 查看数据库用户
mysql> show databases; #查看数据库
mysql> select databases(); #查看是否进入某一个数据库
mysql> use mysql; #切换数据表
mysql> system whoami #切换到linux下
mysql> system ls /
mysql> show tables; #查看数据表
使用help查询命令
#创建数据库
mysql> create database you;
Query OK, 1 row affected (0.00 sec)
#显示数据库
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| you |
| mysql |
| test |
+--------------------+
5 rows in set (0.00 sec)
mysql> SHOW create database you;
+----------+----------------------------------------------------------------+
| Database | Create Database |
+----------+----------------------------------------------------------------+
| you | CREATE DATABASE `you` /*!40100 DEFAULT CHARACTER SET latin1 */ |
+----------+----------------------------------------------------------------+
1 row in set (0.00 sec)
查看数据库字符集
mysql> show create database abc\g;
+----------+----------------------------------------------------------------+
| Database | Create Database |
+----------+----------------------------------------------------------------+
| abc | CREATE DATABASE `abc` /*!40100 DEFAULT CHARACTER SET latin1 */ |
+----------+----------------------------------------------------------------+
1 row in set (0.00 sec)
创建you_gbk数据库字符集gbk
mysql> CREATE DATABASE `you_gbk` DEFAULT CHARACTER SET gbk;
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| you |
| you_gbk |
+--------------------+
7 rows in set (0.00 sec)
创建you_utf8数据库字符集utf8;
mysql> CREATE DATABASE `you_utf8` DEFAULT CHARACTER SET utf8;
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| you_gbk |
| you_utf8 |
+--------------------+
8 rows in set (0.00 sec)
mysql字符集包括字符集和校对规则
字符集是定义mysql字符串存储方式，校对规则定义mysql比较字符串的方式。
删除数据库
mysql> drop database test;
Query OK, 0 rows affected (0.00 sec)
查看数据库用户
mysql> select user,host from mysql.user;
+------+-----------------------+
| user | host |
+------+-----------------------+
| root | 127.0.0.1 |
| | localhost |
| root | localhost |
| | localhost.localdomain |
| root | localhost.localdomain |
+------+-----------------------+
5 rows in set (0.00 sec)
连接数据库
mysql> use mysql
#查看是否进入数据库
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> show tables;
+---------------------------+
| Tables_in_mysql |
+---------------------------+
| columns_priv |
| db |
| event |
| func |
| general_log |
| help_category |
| help_keyword |
| help_relation |
| help_topic |
| host |
| ndb_binlog_index |
| plugin |
| proc |
| procs_priv |
| servers |
| slow_log |
| tables_priv |
| time_zone |
| time_zone_leap_second |
| time_zone_name |
| time_zone_transition |
| time_zone_transition_type |
| user |
+---------------------------+
23 rows in set (0.00 sec)
跳出mysql操作linux
mysql> system whoami
root
mysql> system ls /
bin dev lib misc opt sbin srv usr
boot etc lost+found mnt proc selinux sys var
cgroup home media net root sh tmp
查看mysql数据库有多少张表
mysql> show tables from mysql;
+---------------------------+
| Tables_in_mysql |
+---------------------------+
| columns_priv |
| db |
| event |
| func |
| general_log |
| help_category |
| help_keyword |
| help_relation |
| help_topic |
| host |
| ndb_binlog_index |
| plugin |
| proc |
| procs_priv |
| servers |
| slow_log |
| tables_priv |
| time_zone |
| time_zone_leap_second |
| time_zone_name |
| time_zone_transition |
| time_zone_transition_type |
| user |
+---------------------------+
23 rows in set (0.00 sec)
创建mysql用户
mysql> create user you@‘localhost‘ identified by ‘123456‘;
Query OK, 0 rows affected (0.00 sec)
mysql> select user,host from mysql.user;
+------+-----------------------+
| user | host |
+------+-----------------------+
| root | 127.0.0.1 |
| | localhost |
| root | localhost |
| you | localhost |
| | localhost.localdomain |
| root | localhost.localdomain |
+------+-----------------------+
6 rows in set (0.00 sec)
删除mysql用户
mysql> drop user you1@‘localhost‘;
Query OK, 0 rows affected (0.00 sec)
删除用户
delete from mysql.user where user=‘you‘;
delete from mysql.user where user=‘you‘and user=‘root‘;
用户授权，先创建用户再授权用户
mysql> create user ‘xiaoyou‘@‘localhost‘ identified by ‘123456‘;
mysql> grant all on *.* to ‘xiaoyou‘@‘localhost‘;
Query OK, 0 rows affected (0.00 sec)
查看用户权限 USAGE只有连接的权限
mysql> show grants for oldboy@localhost\G;
*************************** 1. row ***************************
Grants for oldboy@localhost: GRANT USAGE ON *.* TO ‘oldboy‘@‘localhost‘ IDENTIFIED BY PASSWORD ‘*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9‘
1 row in set (0.00 sec)
ERROR:
No query specified
给某个数据库授权
mysql> create user ‘youyou‘@‘localhost‘ identified by ‘123456‘;
Query OK, 0 rows affected (0.00 sec)
mysql> select user,host from mysql.user;
+---------+-----------------------+
| user | host |
+---------+-----------------------+
| root | 127.0.0.1 |
| | localhost |
| root | localhost |
| xiaoyou | localhost |
| youyou | localhost |
| | localhost.localdomain |
| root | localhost.localdomain |
+---------+-----------------------+
8 rows in set (0.00 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| you |
| you_abc |
| you_gbk |
| you_utf8 |
+--------------------+
6 rows in set (0.00 sec)
创建拉丁字符集数据库 mysql默认字符集
mysql> create database data0;
Query OK, 1 row affected (0.00 sec)
linux系统字符集 vim /etc/sysconfig/i18n LANG="zh CN.UTF-8"
创建utf8字符集数据库
mysql> create database data2 default character set utf8 collate utf8_general_ci;
Query OK, 1 row affected (0.00 sec)
创建gbk字符集数据库
mysql> create database data3 default character set gbk collate gbk_chinese_ci;
Query OK, 1 row affected (0.00 sec)
mysql> create database youyou;
Query OK, 1 row affected (0.00 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| you |
| you_abc |
| you_gbk |
| you_utf8 |
| youyou |
+--------------------+
7 rows in set (0.00 sec)
mysql> use youyou
Database changed
mysql> grant all on youyou.* to youyou@localhost;
直接创建用户和授权
mysql> grant all on youyou.* to etian@localhost identified by ‘123456‘;
Query OK, 0 rows affected (0.00 sec)
mysql> grant create,delete,update,insert on aaa.* to you2@localhost identified by ‘123456‘;
Query OK, 0 rows affected (0.00 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| abc |
| anni_bgk |
| data1 |
| data2 |
| mysql |
+--------------------+
6 rows in set (0.00 sec)
收回授权
mysql> revoke insert on youyou.* from ‘jeffrey‘@‘localhost‘;
ERROR 1141 (42000): There is no such grant defined for user ‘jeffrey‘ on host ‘localhost‘
mysql> revoke insert on youyou.* from ‘youyou‘@‘localhost‘;
Query OK, 0 rows affected (0.00 sec)
mysql> show grents for ww@localhost\G
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 ‘grents for ww@localhost‘ at line 1
mysql> show grants for ww@localhost\G;
*************************** 1. row ***************************
Grants for ww@localhost: GRANT USAGE ON *.* TO ‘ww‘@‘localhost‘ IDENTIFIED BY PASSWORD ‘*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9‘
*************************** 2. row ***************************
Grants for ww@localhost: 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 `wwtable`.* TO ‘ww‘@‘localhost‘
2 rows in set (0.00 sec)
查看数据库授权表
mysql> desc mysql.user;
主从mysql授权
生产环境主库用户的授权
grant select, insert, update, delete on blog.* to blog@‘172.20.100.%‘ i dentified by ‘123456‘;
生产环境从库用户的授权
grant select on blog.* to blog@‘10.0.0.%‘ identified by ‘123456‘;
例：
写库
blog you456 3306 10.0.0.7
读库
blog you456 3306 10.0.0.8
创建gbk数据库
CREATE DATABASE anni_bgk CHARACTER SET gbk collate gbk_chinese_ci;
建立测试表test
命令： create talbe test (<字段名1><类型1> <字段n><类型n>.....)
mysql数据库类型
1、INT[(M)]型：正常大小整数类型
2、DOUBLE[(M,D)] [ZEROFILL]型，正常大小双精密浮点数字类型
3、DATE 日期型
4、CHAR（M）型:定长字符串类型，当存储时，总是用空格填满右边到指定的长度
5、BLOB TEXT 类型，最大长度为65535个字符
6、VARCHAR型：变长字符串类型
例：
mysql> create table test(id int(4) not null primary key auto_increment, name char(20) not null);
Query OK, 0 rows affected (0.08 sec)
查看表结构
mysql> desc test;
+-------+----------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+----------------+
| id | int(4) | NO | PRI | NULL | auto_increment |
| name | char(20) | NO | | NULL | |
+-------+----------+------+-----+---------+----------------+
2 rows in set (0.00 sec)
查看建表语句
mysql> show create table from test\G
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 ‘from test‘ at line 1
mysql> show create table test\G
*************************** 1. row ***************************
Table: test
Create Table: CREATE TABLE `test` (
`id` int(4) NOT NULL AUTO_INCREMENT,
`name` char(20) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=gbk
1 row in set (0.00 sec)
插入数据
命令：insert into 表名[(字段名1)(字段名n)] values (值1)(值2)]
mysql> desc test;
+-------+----------+------+-----+---------+----------------+
| 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> insert into test values(1,‘user1‘);
Query OK, 1 row affected (0.00 sec)
mysql> select * from test;
+----+-------+
| id | name |
+----+-------+
| 1 | user1 |
+----+-------+
1 row in set (0.00 sec)
mysql> insert into test (id,name) values (3,‘user3‘);
Query OK, 1 row affected (0.00 sec)
mysql> select * from test;
+----+-------+
| id | name |
+----+-------+
| 1 | user1 |
| 2 | user2 |
| 3 | user3 |
+----+-------+
3 rows in set (0.00 sec)
mysql> insert into test(name) values(‘user4‘)
-> ;
Query OK, 1 row affected (0.00 sec)
mysql> select * from test;
+----+-------+
| id | name |
+----+-------+
| 1 | user1 |
| 2 | user2 |
| 3 | user3 |
| 4 | user4 |
+----+-------+
4 rows in set (0.00 sec)
mysql> insert into test (id,name) values (5,‘user5‘),(6,‘user6‘);
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> select * from test;
+----+-------+
| id | name |
+----+-------+
| 1 | user1 |
| 2 | user2 |
| 3 | user3 |
| 4 | user4 |
| 5 | user5 |
| 6 | user6 |
+----+-------+
6 rows in set (0.00 sec)
mysql> insert into test(name) values(‘user7‘),(‘user8‘);
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> select * from test;
+----+-------+
| id | name |
+----+-------+
| 1 | user1 |
| 2 | user2 |
| 3 | user3 |
| 4 | user4 |
| 5 | user5 |
| 6 | user6 |
| 7 | user7 |
| 8 | user8 |
+----+-------+
8 rows in set (0.00 sec)
you数据库临时备份
[root@localhost mysql]# mysqldump -uroot -p‘root‘ -P 3306 -S /var/lib/mysql/mysql.sock -B you > /tmp/you_bak.sql
查看备份发数据库内容
[root@localhost tmp]# egrep -v "^$|#|\*|--" you_bak.sql
USE `you`;
DROP TABLE IF EXISTS `test`;
CREATE TABLE `test` (
`id` int(4) NOT NULL AUTO_INCREMENT,
`name` char(20) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=9 DEFAULT CHARSET=gbk;
LOCK TABLES `test` WRITE;
INSERT INTO `test` VALUES (1,‘user1‘),(2,‘user2‘),(3,‘user3‘),(4,‘user4‘),(5,‘user5‘),(6,‘user6‘),(7,‘user7‘),(8,‘user8‘);
UNLOCK TABLES;
查询格式
命令：select <字段1，字段2,....> from <表格> where <表达式>
mysql> select * from test limit 0,2 ;
+----+-------+
| id | name |
+----+-------+
| 1 | user1 |
| 2 | user2 |
+----+-------+
2 rows in set (0.00 sec)
mysql> select * from test limit 2,6 ;
+----+-------+
| id | name |
+----+-------+
| 3 | user3 |
| 4 | user4 |
| 5 | user5 |
| 6 | user6 |
| 7 | user7 |
| 8 | user8 |
+----+-------+
6 rows in set (0.00 sec)
查倒顺的前两行
mysql> select * from test order by id desc limit 0,2 ;
+----+-------+
| id | name |
+----+-------+
| 8 | user8 |
| 7 | user7 |
+----+-------+
2 rows in set (0.00 sec)
升顺查找
mysql> select * from test order by id asc limit 0,2 ;
+----+-------+
| id | name |
+----+-------+
| 1 | user1 |
| 2 | user2 |
+----+-------+
2 rows in set (0.00 sec)
查询某一条name记录
mysql> select * from test where name=‘user2‘ ;
+----+-------+
| id | name |
+----+-------+
| 2 | user2 |
+----+-------+
1 row in set (0.00 sec)
查找范围
mysql> select * from test where id >2 and id <6 ;
+----+-------+
| id | name |
+----+-------+
| 3 | user3 |
| 4 | user4 |
| 5 | user5 |
+----+-------+
3 rows in set (0.00 sec)
字符一定要带单引号
mysql> select * from test where id > 7 or id < 2 ;
+----+-------+
| id | name |
+----+-------+
| 1 | user1 |
| 8 | user8 |
+----+-------+
2 rows in set (0.00 sec)
两张表条件关联查询
mysql> select test.id,test.name,test1.name,test1.age from test,test1 where test.name=test1.name;
+----+-------+-------+-----+
| id | name | name | age |
+----+-------+-------+-----+
| 7 | user7 | user7 | 18 |
| 8 | user8 | user8 | 13 |
+----+-------+-------+-----+
2 rows in set (0.00 sec)
mysql> select test.id,test.name,test1.name,test1.age from test,test1 where test.name=test1.name and test1.name=‘xiaoyou‘;
Empty set (0.00 sec)
修改表中的数据
update 表名 set 字段=新值，... where 条件
条件一定要加 where
mysql> update test set name=‘xiaoyou‘ where name=‘user4‘;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from test;
+----+---------+
| id | name |
+----+---------+
| 1 | user1 |
| 2 | user2 |
| 3 | user3 |
| 4 | xiaoyou |
| 5 | user5 |
| 6 | user6 |
| 7 | user7 |
| 8 | user8 |
+----+---------+
8 rows in set (0.01 sec)
不允许这样操作，一定要加条件
mysql> update test set name=‘xiaoyou‘;
Query OK, 7 rows affected (0.00 sec)
Rows matched: 8 Changed: 7 Warnings: 0
mysql> select * from test;
+----+---------+
| id | name |
+----+---------+
| 1 | xiaoyou |
| 2 | xiaoyou |
| 3 | xiaoyou |
| 4 | xiaoyou |
| 5 | xiaoyou |
| 6 | xiaoyou |
| 7 | xiaoyou |
| 8 | xiaoyou |
+----+---------+
8 rows in set (0.00 sec)
恢复错复
[root@localhost mysql]# mysql -uroot -p‘root‘ -P 3306 -S /var/lib/mysql/mysql.sock < /tmp/you_bak.sql
mysql> delete from test where id >3;
Query OK, 3 rows affected (0.01 sec)
mysql> select * from test;
+----+---------+
| id | name |
+----+---------+
| 1 | user1 |
| 2 | user2 |
| 3 | xiaoyou |
+----+---------+
3 rows in set (0.00 sec)
mysql> delete from test where id =3;
Query OK, 1 row affected (0.00 sec)
mysql> select * from test;
+----+-------+
| id | name |
+----+-------+
| 1 | user1 |
| 2 | user2 |
+----+-------+
2 rows in set (0.00 sec)
mysql> select * from test;
+----+-------+
| id | name |
+----+-------+
| 1 | user1 |
| 2 | user2 |
+----+-------+
2 rows in set (0.00 sec)
删除表中的数据
mysql> delete from test where id =1;
Query OK, 1 row affected (0.00 sec)
不充许这样操作，一定要加条件
mysql> select * from test;
+----+-------+
| id | name |
+----+-------+
| 2 | user2 |
+----+-------+
1 row in set (0.00 sec)
mysql> delete from test;
Query OK, 1 row affected (0.00 sec)
直接把表清空
mysql> truncate table test;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from test;
Empty set (0.00 sec)
在表中添加字段
mysql> alter table test1 add sex int(4);
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> alter table test1 add agg int(4) after name;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc test1
-> ;
+-------+----------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+----------------+
| id | int(4) | NO | PRI | NULL | auto_increment |
| name | char(20) | NO | | NULL | |
| agg | int(4) | YES | | NULL | |
| sex | int(4) | YES | | NULL | |
+-------+----------+------+-----+---------+----------------+
4 rows in set (0.00 sec)
删除字段
mysql> alter table test1 drop agg;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc test1;
+-------+----------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+----------------+
| id | int(4) | NO | PRI | NULL | auto_increment |
| name | char(20) | NO | | NULL | |
| sex | int(4) | YES | | NULL | |
+-------+----------+------+-----+---------+----------------+
3 rows in set (0.00 sec)
修改表名
mysql> rename table test1 to test;
Query OK, 0 rows affected (0.00 sec)
mysql> show tables;
+-----------------+
| Tables_in_user1 |
+-----------------+
| test |
+-----------------+
1 row in set (0.00 sec)
mysql>drop table test1;
mysql> insert into test(name) values(‘小游‘);
Query OK, 1 row affected (0.00 sec)
mysql> select * from test;
+----+-----------+------+
| id | name | sex |
+----+-----------+------+
| 1 | user1 | NULL |
| 2 | user2 | NULL |
| 3 | user3 | 16 |
| 4 | user4 | 18 |
| 5 | user5 | 19 |
| 6 | xiaohu | NULL |
| 7 | 小游 | NULL |
+----+-----------+------+
8 rows in set (0.00 sec)
如果不支持中文，一定要先set names gbk,不然出现乱码
改服务端字符集
更改my.cnf参数
[mysqld] 一定要在mysqld下面加
default-character-set=gbk
改客户端字符集
如果不支持中文，可以在备份文件加入set names gbk还原
mysql> set names gbk;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from test where name=‘小游‘;
+----+--------+------+
| id | name | sex |
+----+--------+------+
| 7 | 小游 | NULL |
+----+--------+------+
1 row in set (0.00 sec)
在linux命令行执行mysql里命令
[root@localhost ~]# mysql -uroot -p‘root‘ -S /var/lib/mysql/mysql.sock -e "show databases;"
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| test |
| user1 |
+--------------------+
[root@localhost tmp]# egrep -v "#|\*|--|^$" /tmp/user1_bak.sql
备份数据库
mysqldump -uroot -p‘root‘ -S /var/lib/mysql/mysql.sock -B user1 > /tmp/user1_bak.sql
还原数据库
mysql -uroot -p‘root‘ -S /var/lib/mysql/mysql.sock -B user1 < /tmp/user1_bak.sql
提示：
1、文件本文字符集要正确gb2312
2、文件里加入set names gbk;
3、恢复时执行mysql --defaul-character-set
mysql字符集介绍
GBK 定长 双字节 不是国际标准，支持的系统不少
UTF-8 非定长 1-4字节 广泛支持，MYSQL也使用UTF-8
latin1 MYSQL默认字符集
查看默认节符集
mysql> show variables like ‘character_set%‘;
+--------------------------+----------------------------+
| Variable_name | Value |
+--------------------------+----------------------------+
| character_set_client | latin1 |
| character_set_connection | latin1 |
| character_set_database | latin1 |
| character_set_filesystem | binary |
| character_set_results | latin1 |
| character_set_server | latin1 |
| character_set_system | utf8 |
| character_sets_dir | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.00 sec)
修改后的
mysql> set names gbk;
mysql> show variables like ‘character_set%‘;
+--------------------------+----------------------------+
| Variable_name | Value |
+--------------------------+----------------------------+
| character_set_client | gbk |
| character_set_connection | gbk |
| character_set_database | latin1 |
| character_set_filesystem | binary |
| character_set_results | gbk |
| character_set_server | latin1 |
| character_set_system | utf8 |
| character_sets_dir | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.00 sec)
改服务端字符集
更改my.cnf参数
[mysqld] 一定要在mysqld下面加
default-character-set=gbk
改客户端字符集
如果不支持中文，可以在备份文件加入set names gbk还原
mysql> set names gbk;
Query OK, 0 rows affected (0.00 sec)
1、在服务器my.cnf配置文件里[mysqld]模块下添加字符集配置，重启生效生，创建数据库和表默认都是这个设置的字符集。
2、客户端字符集设置，set names gbk; 这样可以确保插入后的中文 ，不出现乱码，对执行set names gbk;之前插入的中文无效。
在linux登陆和mysql下的set names gbk;相同
[root@localhost tmp]# mysql -uroot -p‘root‘ -S /var/lib/mysql/mysql.sock --default-character-set=gbk
mysql> show variables like ‘character_set%‘;
+--------------------------+--------------------------------------------+
| Variable_name | Value |
+--------------------------+--------------------------------------------+
| character_set_client | gbk #客户端字符集set names gbk; |
| character_set_connection | gbk #连接字符集 set names gbk; |
| character_set_database | gbk #数据库字符集 配置文件或建库建表指定 |
| character_set_filesystem | binary |
| character_set_results | gbk #返回结果字符集，set names gbk; |
| character_set_server | gbk #服务器字符集 配置文件或建库建表指定 |
| character_set_system | utf8 |
| character_sets_dir | /usr/share/mysql/charsets/ |
+--------------------------+--------------------------------------------+
8 rows in set (0.00 sec)
linux命令行查看表
[root@localhost tmp]# mysql -uroot -p‘root‘ -S /var/lib/mysql/mysql.sock -e "select * from user1.test;" user1是数据库 test是数据库里的表
+----+--------+------+
| id | name | sex |
+----+--------+------+
| 1 | user1 | NULL |
| 2 | user2 | NULL |
| 3 | user3 | 16 |
| 4 | user4 | 18 |
| 5 | user5 | 19 |
| 6 | xiaohu | NULL |
| 7 | 小游 | NULL |
+----+--------+------+
[root@localhost ~]# mysql -uroot -p‘root‘ -S /var/lib/mysql/mysql.sock -e "set names gbk;select * from data1.test;"
+----+--------+------+-----+------+
| id | name | sex | age | org |
+----+--------+------+-----+------+
| 1 | user1 | NULL | 0 | NULL |
| 2 | user2 | NULL | 26 | NULL |
| 4 | user4 | NULL | 0 | NULL |
| 5 | user5 | NULL | 0 | NULL |
| 6 | user6 | NULL | 0 | NULL |
| 7 | 小游 | NULL | 0 | NULL |
| 8 | 小游 | NULL | 0 | NULL |
+----+--------+------+-----+------+
查看mysql支持引擎
mysql> show engines;
+------------+---------+------------------------------------------------------------+--------------+------+------------+
| Engine | Support | Comment | Transactions | XA | Savepoints |
+------------+---------+------------------------------------------------------------+--------------+------+------------+
| MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO |
| CSV | YES | CSV storage engine | NO | NO | NO |
| MyISAM | DEFAULT | Default engine as of MySQL 3.23 with great performance | NO | NO | NO |
| InnoDB | YES | 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 |
+------------+---------+------------------------------------------------------------+--------------+------+------------+
5 rows in set (0.00 sec)
备份数据库
普通备份
[root@localhost ~]# mysqldump -uroot -p‘root‘ data1 >/tmp/data_bak.sql
[root@localhost ~]# mysqldump -uroot -p‘root‘ -S /var/lib/mysql/mysql.sock data1|gzip >/tmp/data$(date +%F).sql.gz
备份多个库 data1 you_bgk
[root@localhost tmp]# mysqldump -uroot -p‘root‘ -S /var/lib/mysql/mysql.sock -B data1 you_bgk|gzip >/tmp/database_bak$(date +%F).sql.gz
如果分库备份
备份表 data1是库名 test是表名
[root@localhost tmp]# mysqldump -uroot -p‘root‘ -S /var/lib/mysql/mysql.sock data1 test >/tmp/test_table.sql
备份多张表 data1是库名 test是表名 aaa是表名
[root@localhost tmp]# mysqldump -uroot -p‘root‘ -S /var/lib/mysql/mysql.sock data1 test aaa >/tmp/test_table.sql
备份库和表结构
[root@localhost tmp]# mysqldump -uroot -p‘root‘ -S /var/lib/mysql/mysql.sock -d data1 >/tmp/aaa.sql
[root@localhost tmp]# egrep -v "^$|#|\*|--" aaa.sql
DROP TABLE IF EXISTS `aaa`;
CREATE TABLE `aaa` (
`name` char(20) NOT NULL,
`age` int(3) NOT NULL,
PRIMARY KEY (`name`)
) ENGINE=MyISAM DEFAULT CHARSET=gbk;
DROP TABLE IF EXISTS `test`;
CREATE TABLE `test` (
`id` int(3) NOT NULL AUTO_INCREMENT,
`name` char(20) NOT NULL,
`sex` int(4) DEFAULT NULL,
`age` int(4) NOT NULL,
`org` int(4) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=29 DEFAULT CHARSET=gbk;
让备份结果输出少一些 --compact
[root@localhost tmp]# mysqldump -uroot -p‘root‘ -S /var/lib/mysql/mysql.sock --compact -d data1 >/tmp/aaa.sql
恢复数据库
1、source 命令恢复
用source加linux命令行的路径直接恢复mysql数据
mysql> source /tmp/tmp.sql
Database changed
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.05 sec)
Query OK, 0 rows affected (0.00 sec)
[root@localhost tmp]# mysql -uroot -p‘root‘ -S /var/lib/mysql/mysql.sock -e "source /tmp/tmp.sql "
2、mysql命令导入更新恢复
[root@localhost tmp]# mysql -uroot -p‘root‘ -S /var/lib/mysql/mysql.sock < /tmp/tmp.sql
假定开发人员让我们插入数据到数据库，可能是邮件发的，内容可能是字符串或都是下面的文件，带中文
sql文里没有use data1的时候，在导入时路径就必须指定数据库名
[root@localhost tmp]# mysql -uroot -p‘root‘ -S /var/lib/mysql/mysql.sock < /tmp/aa.sql
mysql -e实现非交互式对话
[root@localhost tmp]# mysql -uroot -p‘root‘ -e "select * from data1.test;"
echo 和 -e 类似命令
[root@localhost tmp]# echo "show status" | mysql -uroot -p‘root‘ -S /var/lib/mysql/mysql.sock
[root@localhost tmp]#cat | mysql -uroot -p‘root‘ -S /var/lib/mysql/mysql.sock << EOF
show databases;
EOF
[root@localhost tmp]# cat | mysql -uroot -p‘root‘ -S /var/lib/mysql/mysql.sock << EOF
> show databases;
> EOF
Database
information_schema
anni_bgk
data0
data1
data2
data3
mysql
you
you1
[root@localhost tmp]# mysql -uroot -p‘root‘ -e "select * from data1.test;select * from data1.aaa;"
查看mysql状态
[root@localhost tmp]# mysql -uroot -p‘root‘ -S /var/lib/mysql/mysql.sock -e "show processlist;"
[root@localhost tmp]# mysql -uroot -p‘root‘ -S /var/lib/mysql/mysql.sock -e "show full processlist;"
+----+------+-----------+-------+---------+------+-------+-----------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+-----------+-------+---------+------+-------+-----------------------+
| 3 | root | localhost | data1 | Sleep | 168 | | NULL |
| 7 | root | localhost | NULL | Query | 0 | NULL | show full processlist |
+----+------+-----------+-------+---------+------+-------+-----------------------+
mysql sleep过多的问题
[root@localhost tmp]# mysql -uroot -p‘root‘ -S /var/lib/mysql/mysql.sock -e "show full processlist;" |egrep -v "sleep"
Id User Host db Command Time State Info
3 root localhost data1 Sleep 291 NULL
10 root localhost NULL Query 0 NULL show full processlist
查看mysql参数
[root@localhost tmp]# mysql -uroot -p‘root‘ -S /var/lib/mysql/mysql.sock -e "show variables"
查看mysql状态
[root@localhost tmp]# mysql -uroot -p‘root‘ -S /var/lib/mysql/mysql.sock -e "show status"
查看mysql各个查询状态
[root@localhost tmp]# mysql -uroot -p‘root‘ -S /var/lib/mysql/mysql.sock -e "show global status"
过滤Com_select|Com_insert|Com_delete|Com_update
[root@localhost tmp]# mysql -uroot -p‘root‘ -S /var/lib/mysql/mysql.sock -e "show global status"| egrep "Com_select|Com_insert|Com_delete|Com_update"
Com_delete 0
Com_delete_multi 0
Com_insert 0
Com_insert_select 0
Com_select 18
Com_update 0
Com_update_multi 0
查看引擎的缓冲区
[root@localhost tmp]# mysql -uroot -p‘root‘ -S /var/lib/mysql/mysql.sock -e "show variables;"|grep key_buffer
key_buffer_size 8384512
设置缓冲区
[root@localhost tmp]# mysql -uroot -p‘root‘ -S /var/lib/mysql/mysql.sock -e "set global key_buffer_size = 32776192;"
[root@localhost tmp]# mysql -uroot -p‘root‘ -S /var/lib/mysql/mysql.sock -e "show variables;"|grep key_buffer
key_buffer_size 32776192 #32M
源码包
[root@localhost tmp]# vim /data/3306/my.cnf
sedi -i ‘s#key_buffer_size = 16M# key_buffer_size = 32M#g‘ /data/3306/my.cnf
安装mysql
建立mysql帐号
groupadd mysql;
useradd -s /sbin/nologin -g mysql -M mysql;
-s /sbin/nologin 表示禁止该用户登陆，加强安全
-g mysql 指定属于mysql组
-M 表示不创建用户家目录
查看创建的用户
tail -1 /etc/passwd
mysql:x:500:500::/home/mysql:/sbin/nologin
编绎之前安装gcc yum -y install gcc*
yum -y install ncurses-devel
./configure \
--prefix=/usr/local/mysql \
--with-unix-socket-path=/usr/local/mysql/tmp/mysql.sock \
--localstatedir=/usr/local/mysql/data \
--enable-assembler \
--enable-thread-safe-client \
--with-mysqld-user=mysql \
--with-big-tables \
--without-debug \
--with-prthread \
--enable-assembler \
--with-extra-charsets=complex \
--with-readline \
--with-ssl \
--with-embedded-server \
--enable-local-infile \
--with-plugins=partition,innobase \
--with-plugin-PLUGIN \
--with-mysqld-ldflags=-all-static \
--with-client-ldflags=-all-static
make && make install
ls /usr/local/mysql/
killall mysqld
以前的mysql单实例清除
生产环境多实例 内存16G，双CPU 八核，磁盘6*600G sas 15k
主从同步
mysql> grant replication slave on *.* to rep@‘172.20.100.%‘ identified by ‘123456‘;
Query OK, 0 rows affected (0.00 sec)
replication slave 为mysql同步的必须权限，此处不要授权all
*.*所有库所有表
mysql> select user,host from mysql.user;
+------+-----------------------+
| user | host |
+------+-----------------------+
| root | 127.0.0.1 |
| rep | 172.20.100.% |
| | localhost |
| root | localhost |
| | localhost.localdomain |
| root | localhost.localdomain |
+------+-----------------------+
6 rows in set (0.00 sec)
生产环境时，操作主从复制，需要申请停机时间。锁表会影响业务
锁表主库
mysql> flush tables with read lock;
Query OK, 0 rows affected (0.00 sec)
锁表失效时间以下
mysql> show variables like ‘%timeout%‘;
+----------------------------+-------+
| Variable_name | Value |
+----------------------------+-------+
| connect_timeout | 10 |
| delayed_insert_timeout | 300 |
| innodb_lock_wait_timeout | 120 |
| innodb_rollback_on_timeout | OFF |
| interactive_timeout | 28800 |
| net_read_timeout | 30 |
| net_write_timeout | 60 |
| slave_net_timeout | 3600 |
| table_lock_wait_timeout | 50 |
| wait_timeout | 28800 |
+----------------------------+-------+
10 rows in set (0.00 sec)
mysql> show master status;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000004 | 536 | | |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
[root@localhost ~]# mysqldump -uroot -p -S /data/3306/mysql.sock -A -B |gzip >/server/backup/mysql_bak.$(date +%F).sql.gz
-A 备份所有库
-B 添加参数比如DROP等
[root@localhost backup]# ls
mysql_bak.2017-04-21.sql.gz
解锁表
mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)
从库操作
[root@localhost backup]# mysql -uroot -p -S /data/3307/mysql.sock < /server/backup/mysql_bak.2017-04-21.sql
如果需要停掉从库，执行以下命令
[root@localhost ~]# mysql -uroot -p -S /data/3307/mysql.sock -e "stop slave;
方法1
cat |mysql -uroot -p‘root‘ -S /data/3307/mysql.sock<< EOF
CHANGE MASTER TO
MASTER_HOST=‘172.20.100.134‘,
MASTER_PORT=3306,
MASTER_USER=‘rep‘,
MASTER_PASSWORD=‘123456‘,
MASTER_LOG_FILE=‘mysql-bin.000001‘,
MASTER_LOG_POS=460;
EOF
方法2
mysql> CHANGE MASTER TO
-> MASTER_HOST=‘172.20.100.134‘,
-> MASTER_PORT=3306,
-> MASTER_USER=‘rep‘,
-> MASTER_PASSWORD=‘123456‘,
-> MASTER_LOG_FILE=‘mysql-bin.000004‘,
-> MASTER_LOG_POS=536;
Query OK, 0 rows affected (0.10 sec)
启动同步开关
[root@localhost backup]# mysql -uroot -p -S /data/3307/mysql.sock -e "start slave;"
[root@localhost backup]# mysql -uroot -p -S /data/3307/mysql.sock -e "show slave status\G"
Enter password:
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 172.20.100.134
Master_User: rep
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000004
Read_Master_Log_Pos: 696
Relay_Log_File: relay-bin.000002
Relay_Log_Pos: 411
Relay_Master_Log_File: mysql-bin.000004
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB: mysql
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 696
Relay_Log_Space: 560
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0 #和主库比同步延迟的秒数，这个参数很重要。
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
查看状态
[root@localhost backup]# mysql -uroot -p‘123456‘ -S /data/3307/mysql.sock -e "show slave status\G" | egrep "Slave_IO_Running|Slave_SQL_Running"
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
如果报错1007
[root@localhost ~]# mysql -uroot -p -S /data/3307/mysql.sock -e "stop slave;"
[root@localhost ~]# mysql -uroot -p -S /data/3307/mysql.sock -e "set global sql_slave_skip_counter =1 ;" 1就是忽略1次更新，不推荐
[root@localhsot ~]# mysql -uroot -p -S /data/3307/mysql.sock -e "start slave;"
不加班在工作时间轻松配置从库
在服务器压力比较小的时候备份
1、锁表备份全备一份
2、锁表前后取得show master status值记录日志里
binlog-ignore-db=information_schema
binlog-ignore-db=mysql
主从库授权
在生产环境以下参考
主库（提供写服务） 用户user 密码user123 ip 172.20.100.1 port 3306
从库（提供读服务） 用户user 密码user123 ip 172.20.100.2 port 3306
mysql常用命令