时间：2021-07-01 10:21:17 帮助过：2人阅读
守护线程
应用线程
优化数据库性能
缓存
线程重用
硬件
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常用命令
标签：mysql