.cn 则无 warnings soft 备份恢复 tables 生效 ros radi
一,MySQL 用户管理.
一个 MySQL 数据库里可以跑多个库,总不能给所有人的程序员 root 用户,则可以给他们单独的用户访问数据库.
创建用户:(grant all on *.* to 'user1'是把所有库的权限给'user1,他的来源 Ip 是 127.0.0.1,他的密码是 lty123456')
(第一个 * 是库名,如果你写成 mysql.* 那就是对 mysql 库的所有权限)
(来源 ip 也可以写成 % ,表示来源的所有 ip)
(grant 这种语句是不会记录到命令历史里去的,因为不安全.)
默认是 sock 连接,所以必须要加 - h 指定 ip,如果不想指定可以把来源 ip 换成 localhost,localhost 就是针对的 sock.
mysql> grant all on *.* to 'user1@127.0.0.1' identified by 'lty123456';
Query OK, 0 rows affected (0.33 sec)
(把之前的 all 换成权限则表示不把所有的权限给他,只给他指定的权限.)
mysql> show grants; (显示所有的授权)
mysql> grant SELECT,UPDATE,INSERT on rxr.* to 'user3'@'192.168.52.101' identified by 'westos123';
Query OK, 0 rows affected (0.00 sec)
mysql> show grants for user2@192.168.52.101; (查看指定用户的授权)
+----------------------------------------------------------------------------------------------------------------------------------------+
| Grants for root@localhost |
+----------------------------------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY PASSWORD '*1836D7557E753782F1509748BD403456701A0D2F' WITH GRANT OPTION |
| GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION |
+----------------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)
grants 命令是可以添加授权的,比如 192.168.52.101 是来源 ip,我想把 192.168.52.102 也设置为来源 ip,可以复制 grants 的 2 个字符串,把 ip 改成 192.168.52.102,则可以直接和 192.168.52.101 的密码一样,授权一样.
+----------------------------------------------------------------------------------------------------------------------------+
| Grants for user2@192.168.52.101 |
+----------------------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'user2'@'192.168.52.101' IDENTIFIED BY PASSWORD '*4B593B8F245CCB70478E5B1FE8BC06557A52FA7E' |
+----------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
二,常用 sql 语句
常见的数据库引擎有 2 种,
MyISAM 和 InooDB
MyISAM 的特点是会自动帮你统计行数,所以你用下面的命令 select count(*) 命令就会很快显示出来.
InooDB 如果要用 select count(*) 或 select * 时就很慢.
所以尽量减少 select count(*) 和 select * 这种查询所有的操作.
常用 sql 语句
查询:
mysql> select count(*) from mysql.user; (查询一个数据库里的表有多少行)
mysql> select * from mysql.db\G; (查看 mysql.db 的所有内容,加 \ G)
+----------+
| count(*) |
+----------+
| 11|
+----------+
1 row in set (0.24 sec)
mysql> select user from mysql.user; (查看指定表的行数)
*************************** 1. row ***************************
Host: %
Db: test
User:
Select_priv: Y
Insert_priv: Y
Update_priv: Y
Delete_priv: Y
Create_priv: Y
Drop_priv: Y
Grant_priv: N
References_priv: Y
Index_priv: Y
Alter_priv: Y
Create_tmp_table_priv: Y
Lock_tables_priv: Y
Create_view_priv: Y
Show_view_priv: Y
Create_routine_priv: Y
Alter_routine_priv: N
Execute_priv: N
Event_priv: Y
Trigger_priv: Y
*************************** 2. row ***************************
Host: %
Db: test\_%
User:
Select_priv: Y
Insert_priv: Y
Update_priv: Y
Delete_priv: Y
Create_priv: Y
Drop_priv: Y
Grant_priv: N
References_priv: Y
Index_priv: Y
Alter_priv: Y
Create_tmp_table_priv: Y
Lock_tables_priv: Y
Create_view_priv: Y
Show_view_priv: Y
Create_routine_priv: Y
Alter_routine_priv: N
Execute_priv: N
Event_priv: Y
Trigger_priv: Y
*************************** 3. row ***************************
Host: '192.168.52.101'
Db: rxr
User: 'user2'
Select_priv: Y
Insert_priv: Y
Update_priv: Y
Delete_priv: N
Create_priv: N
Drop_priv: N
Grant_priv: N
References_priv: N
Index_priv: N
Alter_priv: N
Create_tmp_table_priv: N
Lock_tables_priv: N
Create_view_priv: N
Show_view_priv: N
Create_routine_priv: N
Alter_routine_priv: N
Execute_priv: N
Event_priv: N
Trigger_priv: N
*************************** 4. row ***************************
Host: '192.168.52.101'
Db: rxr
User: 'user3'
Select_priv: Y
Insert_priv: Y
Update_priv: Y
Delete_priv: N
Create_priv: N
Drop_priv: N
Grant_priv: N
References_priv: N
Index_priv: N
Alter_priv: N
Create_tmp_table_priv: N
Lock_tables_priv: N
Create_view_priv: N
Show_view_priv: N
Create_routine_priv: N
Alter_routine_priv: N
Execute_priv: N
Event_priv: N
Trigger_priv: N
4 rows in set (0.00 sec)
mysql> select user from db; (查看 db 表里 user 的字段,db 是在 mysql 库里,如果没有 use 到 mysql 里,还可以用 mysql.db 代替 db)
+-----------------+
| user |
+-----------------+
| user1@127.0.0.1 |
| lty |
| root |
| user2 |
| root |
| |
| root |
| |
| root |
| 'user2' |
| 'user3' |
+-----------------+
11 rows in set (0.00 sec)
mysql> select db,user from mysql.db ; (也可以同时查看 2 个字段的,db 和 user 中间用逗号分隔)
+-------------+
| user |
+-------------+
| |
| |
| 'user2' |
| 'user3' |
+-------------+
4 rows in set (0.00 sec)
mysql> select * from mysql.db where host like '192.168.%'; (这是一条模糊查询选项,查找 db 表包含 192.168% 的选项,)
+---------+-------------+
| db | user |
+---------+-------------+
| test | |
| test\_% | |
| rxr | 'user2' |
| rxr | 'user3' |
+---------+-------------+
4 rows in set (0.00 sec)
增加:
mysql> desc lty.rxr; (我们之前在 lty 这个数据库里增加了一个 rxr 的表)
mysql> insert into lty.rxr values (1,'abc');(因为有 id 和 name 两个字段,所有分别加入 2 个字符,name 可能是个字符串,所以加单引号)
+-------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| id | int(4) | YES | | NULL | |
| name | char(40) | YES | | NULL | |
+-------+----------+------+-----+---------+-------+
2 rows in set (0.00 sec)
Query OK, 1 row affected (0.33 sec)
mysql> select * from lty.rxr; (查看表里的内容)
也可以根据条件批量的修改或删除
+------+------+
| id | name |
+------+------+
| 1 | abc |
+------+------+
1 row in set (0.00 sec)
mysql> select * from lty.rxr; (我们增加三行字段)
批量的修改:
+------+------+
| id | name |
+------+------+
| 1 | abc |
| 1 | 234 |
| 1 | 234 |
+------+------+
3 rows in set (0.00 sec)
mysql> update lty.rxr set name='aaa' where id=1; (把所有 id 为 1 的,name 都修改为'aaa')
批量删除:
Query OK, 3 rows affected (0.00 sec)
Rows matched: 3 Changed: 3 Warnings: 0
mysql> select * from lty.rxr;
+------+------+
| id | name |
+------+------+
| 1| aaa |
| 1| aaa |
| 1| aaa |
+------+------+
3 rows in set (0.00 sec)
mysql> delete from lty.rxr where id=1; (删除所有 id=1 的字段)
清空和删除:清空只是把数据清空,但是表和创建表时的字段还保留,删除时把表和字段全部干掉.这两个命令用之前一定三思!!
Query OK,
3 rows affected(0.00 sec) mysql > select * from lty.rxr;
Empty set(0.00 sec)
清空一个表:
删除一个表和一个库:
mysql> truncate table lty.rxr;
Query OK, 0 rows affected (0.00 sec)
mysql> show databases; (查看库,已经没有 lty 这个库)
mysql> drop table lty.rxr;
Query OK, 0 rows affected (0.00 sec)
mysql> drop database lty;
Query OK, 0 rows affected (0.18 sec)
三,MySQL 数据可备份恢复
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| rxr |
| test |
+--------------------+
5 rows in set (0.00 sec)
如果因为自己的误操作导致数据库丢失,如果之前备份了数据库,则无伤大雅,所以这是很重要的知识点.
mysqldump 不仅可以备份数据库,也可也备份表.但是数据太大的我们有更好的办法,可以看拓展.
下面我们来做 mysqldump 的实验.
[root@lnmp ~]# ll /usr/local/mysql/bin/mysqldump (bin 下的这个命令就是用来备份和恢复数据库)
-rwxr-xr-x. 1 7161 31415 8273183 3 月 18 2017 /usr/local/mysql/bin/mysqldump
备份数据库:
[root@lnmp ~]# mysqldump -uroot -paminglinux rxr > /tmp/mysql.sql (把数据库 rxr 备份到 / tmp 下并改名为 mysql.sql)
恢复数据库:
[root@lnmp ~]# mysql -uroot -paminglinux rxr2 < /tmp/mysql.sql (把 / tmp / 下的 mysql.sql 恢复到数据库中,并改名 rxr2)
备份表:
[root@lnmp ~]# mysqldump -uroot -paminglinux mysql user > /tmp/user.sql (把数据库 mysql 下的 user 表备份到 / tmp/user.sql)
恢复表:
[root@lnmp ~]# mysql -uroot -paminglinux mysql user2 < /tmp/user.sql (吧 / tmp 下的 user.sql 恢复到数据库改名 user2)
(我们可以发现,备份用 mysqldump, 恢复用 mysql,不要搞混了.在恢复库或者表时,观察他的参数可以发现,如果存在库 / 表,先 drop 掉,然后创建库 / 表,然后再每一行的插入,也是在用 sql 语句操作)
[root@lnmp ~]# mysql -uroot -paminglinux mysql (这样登录数据后加库名,直接进入当指定库下)
备份所有的库:(-A 表示 all)
[root@lnmp ~]# mysqldump -uroot -paminglinux -A >/tmp/123.sql
只备份表的结构,不备份数据:
[root@lnmp ~]# mysqldump -uroot -p123456 -d mysql > /tmp/mysql.d.sql
四,MySQL 调优
MySQL 调优可以从几个方面来做:
1. 架构层:
做从库,实现读写分离;
2. 系统层次:
增加内存;
给磁盘做 raid0 或者 raid5 以增加磁盘的读写速度;
可以重新挂载磁盘,并加上 noatime 参数,这样可以减少磁盘的 i/o;
3. MySQL 本身调优:
(1) 如果未配置主从同步,可以把 bin-log 功能关闭,减少磁盘 i/o
(2) 在 my.cnf 中加上 skip-name-resolve, 这样可以避免由于解析主机名延迟造成 mysql 执行慢
(3) 调整几个关键的 buffer 和 cache.调整的依据,主要根据数据库的状态来调试.如何调优可以参考 5.
4. 应用层次:
查看慢查询日志,根据慢查询日志优化程序中的 SQL 语句,比如增加索引
5. 调整几个关键的 buffer 和 cache
1) key_buffer_size 首先可以根据系统的内存大小设定它,大概的一个参考值:1G 以下内存设定 128M;2G/256M; 4G/384M;8G/1024M;16G/2048M. 这个值可以通过检查状态值 Key_read_requests 和 Key_reads, 可以知道 key_buffer_size 设置是否合理.比例 key_reads / key_read_requests 应该尽可能的低,至少是 1:100,1:1000 更好 (上述状态值可以使用 SHOW STATUS LIKE'key_read%'获得).注意:该参数值设置的过大反而会是服务器整体效率降低!
2) table_open_cache 打开一个表的时候,会临时把表里面的数据放到这部分内存中,一般设置成 1024 就够了,它的大小我们可以通过这样的方法来衡量: 如果你发现 open_tables 等于 table_cache,并且 opened_tables 在不断增长,那么你就需要增加 table_cache 的值了 (上述状态值可以使用 SHOW STATUS LIKE'Open%tables'获得).注意,不能盲目地把 table_cache 设置成很大的值.如果设置得太高,可能会造成文件描述符不足,从而造成性能不稳定或者连接失败.
3) sort_buffer_size 查询排序时所能使用的缓冲区大小, 该参数对应的分配内存是每连接独占! 如果有 100 个连接,那么实际分配的总共排序缓冲区大小为 100 × 4 = 400MB.所以,对于内存在 4GB 左右的服务器推荐设置为 4-8M.
4) read_buffer_size 读查询操作所能使用的缓冲区大小.和 sort_buffer_size 一样,该参数对应的分配内存也是每连接独享!
5) join_buffer_size 联合查询操作所能使用的缓冲区大小,和 sort_buffer_size 一样,该参数对应的分配内存也是每连接独享!
6) myisam_sort_buffer_size 这个缓冲区主要用于修复表过程中排序索引使用的内存或者是建立索引时排序索引用到的内存大小,一般 4G 内存给 64M 即可.
7) query_cache_size MySQL 查询操作缓冲区的大小,通过以下做法调整:SHOW STATUS LIKE 'Qcache%'; 如果 Qcache_lowmem_prunes 该参数记录有多少条查询因为内存不足而被移除出查询缓存.通过这个值,用户可以适当的调整缓存大小.如果该值非常大,则表明经常出现缓冲不够的情况,需要增加缓存大小; Qcache_free_memory: 查询缓存的内存大小,通过这个参数可以很清晰的知道当前系统的查询内存是否够用,是多了,还是不够用,我们可以根据实际情况做出调整.一般情况下 4G 内存设置 64M 足够了.
8) thread_cache_size 表示可以重新利用保存在缓存中线程的数,参考如下值:1G —> 8 2G —> 16 3G —> 32 >3G —> 64
除此之外,还有几个比较关键的参数:
9) thread_concurrency 这个值设置为 cpu 核数的 2 倍即可
10) wait_timeout 表示空闲的连接超时时间,默认是 28800s,这个参数是和 interactive_timeout 一起使用的,也就是说要想让 wait_timeout 生效,必须同时设置 interactive_timeout,建议他们两个都设置为 10
11) max_connect_errors 是一个 MySQL 中与安全有关的计数器值,它负责阻止过多尝试失败的客户端以防止暴力破解密码的情况.与性能并无太大关系.为了避免一些错误我们一般都设置比较大,比如说 10000
12) max_connections 最大的连接数,根据业务请求量适当调整,设置 500 足够
13) max_user_connections 是指同一个账号能够同时连接到 mysql 服务的最大连接数.设置为 0 表示不限制.通常我们设置为 100 足够
一位同学调优的经历: http://ask.apelearn.com/question/11281
恢复误操作的数据: http://www.centoscn.com/mysql/2015/0204/4630.html MySQL(用户管理,常用 sql 语句,数据库备份恢复,MySQL 调优,恢复误操作数据)
来源: http://www.bubuko.com/infodetail-2462756.html