小皮博客 | Xiaopi's Blog

14-mysql基本运维操作指南

MySQL基本运维操作指南, 纯工具文
为了方便区分 mysql> 表示为MySQL命令行模式,其他为shell终端模式

杂项

MyISAM引擎是对表进行锁定的,InnoDB是支持表的行锁定的.

MyISAM无法热备份(处理数据时无法进行备份)

不支持自定义数据类型/XML/OLAP(实时分析处理,online analytical processing)

配置root用户

mysqladmin -u root password ‘secret’
mysqladmin -u root -h hostname password ‘secret’

my.cnf首先可以配置远程访问控制,而后需要在mysql的mysql库中配置远程访问信息

查看Mysql服务运行状态

mysql> status

快速复制MySQL数据库

1
2
3
4
5
6
7
8
# 创建一个新库
mysql> CREATE DATABASE `newDB` DEFAULT CHARACTER SET UTF8 COLLATE UTF8_GENERAL_CI;
# 本地快速拷贝
shell# mysqldump olddb -uroot -ppassword --add-drop-table | mysql -uuser -ppassword
# 远程快速拷贝
shell# mysqldump olddb -uroot -ppassword --add-drop-table | mysql -hlocalhost -uuser -ppassword
# 更加常规的写法
shell# mysqldump -u root --password=xxx dbname | mysql -u root --password=yyy destinationhost dbname

新手模式

mysql -uroot -p –i-am-a-dummy # 我是个新手

开启新手模式之后,执行删除全表或者修改全表的操作时将会提示

ERROR 1175 (HY000): You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column

mysqladmin

执行SQL文件

mysql -u root -p dbname < backupfile.sql
mysql -u root -p -default-character-set=latin1 name < backup.sql

导出SQL文件

mysqldump [options] dbname > backupfile.sql # -default-character-set=utf8 默认就是utf8

数据库间复制表

1
2
3
4
5
6
# 创建一个设置相同的数据表  
CREATE TABLE new_table_name LIKE old_db.old_table_name;
# 插入数据
INSERT INTO new_table_name SELECT * FROM old_db.old_table_name;
# 直接进行数据库表间复制
INSERT INTO new_db.new_table_name SELECT field1,field2 .. fieldn FROM old_db.old_table_name;

MySQL全文索引

ALTER TABLE tablename ADD FULLTEXT(column1, column2); # 只支持MyISAM引擎

查看MySQl连接协议

shell# mysql -u root -p –protocol=tcp | socket | pipe | memory # 使用指定协议启动连接 

查看当前使用的协议状态

mysql> status
Connection: Localhost via UNIX socket #关注这一行即可

新安装MySQL的常规流程(非常重要!)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
use mysql; 
# 查看系统所有MySQL的user账户
SELECT user, host, password FROM user;
# 修改所有名称为root的账户的密码
UPDATE user SET password = PASSWORD('secret') WHERE user = 'root';
# 修改系统默认的root账户
UPDATE user SET user = 'mynewrootname' WHERE user = 'root';
# 删除匿名用户
DELETE FROM user WHERE user = '';
# 修改所有不需要密码登陆的账号的密码
UPDATE user SET password = PASSWORD('secret') WHERE password = '';
# 查看可以从任意位置注册的用户的处理逻辑,
# MySQL只有在它不能确定主机名的时候才检查IP地址
SELECT user, host, password FROM user WHERE host = '%';
# 使mysql库中的配置反向刷新到RAM中
FLUSH PRIVILEGES;

常见权限分配

创建数据库及用户

1
2
CREATE DATABASE forum;
GRANT ALL ON forum.* TO forumadmin@localhost IDENTIFIED BY 'secret';

关闭防火墙

1
2
3
4
5
6
7
8
9
10
11
12
13
shell# service iptables stop; # 关闭

#======================================
iptables: Setting chains to policy ACCEPT: filter [ OK ]
iptables: Flushing firewall rules: [ OK ]
iptables: Unloading modules: [ OK ]
#======================================

shell# service iptables status # 检查是否关闭成功

#======================================
iptables: Firewall is not running.
#======================================

创建远程root用户

1
2
3
4
5
6
7
# 与root一致了,一般不建议这样做
GRANT ALL ON *.* TO remoteRoot@'%' IDENTIFIED BY 'newrootpassword' WITH GRANT OPTION;

# 只允许创建以自己用户名开头的数据库, 便于管理
GRANT USAGE ON *.* TO username@localhost IDENTIFIED BY 'userpassword';

INSERT INTO mysql.db (HOST, Db, User, Select_priv, Insert_priv, Update_priv, Delete_priv, Create_priv, Drop_priv, Grant_priv,Reference_priv, Index_priv, Alter_priv, Create_tmp_table_priv, Lock_tables_priv, Create_view_priv, Show_view_priv) VALUES ('localhost', 'username%', 'username', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'N', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y');

忘记root用户密码

1
2
3
4
5
6
7
shell# /etc/rc.d/mysql stop # 停止MySQL服务  
shell# vim /etc/my.cnf # 找到对应的行
...
[mysqld]
skip_grant_tables
...
shell# /etc/rc.d/mysql start

resolveip & hostname命令行

1
2
3
4
5
6
7
8
9
10
11
12
shell# resolveip localhost.localdomain:

#================================
IP address of localhost.localdomain is 127.0.0.1
IP address of localhost.localdomain is 127.0.0.1
#================================

shell# resolveip 127.0.0.1

#================================
Host name of 127.0.0.1 is localhost, localhost.localdomain, localhost4, localhost4.localdomain4
#================================

查看权限

mysql> show GRANTS FOR forumadmin@’%’;

+———————————————————————————————————–+
| Grants for forumadmin@% |
+———————————————————————————————————–+
| GRANT USAGE ON . TO ‘forumadmin‘@’%’ IDENTIFIED BY PASSWORD ‘14E65567ABDB5135D0**‘ |
| GRANT ALL PRIVILEGES ON forum.* TO ‘forumadmin‘@’%’ |
+———————————————————————————————————–+

连接困难的原因 参见11.6.1

执行MySQLd服务的Linux系统用户

file /etc/my.cnf
[mysqld]
user = mysql

检查mysqld的运行用户

shell# ps au | grep mysqld # 最好的情况是mysqld_safe由(必须由)root执行,其他都由mysql执行

[mysqladmin] 用法

查看mysqladmin用法

mysqladmin -help # 会给出所有命令的解释

创建和删除数据库

mysqladmin -uroot -p create newDatabase #创建数据库名为 newDatabase

修改用户名密码

mysqladmin -u root -h localhost password newpassword # 必须给出MySQL服务器主机的完整网络名

重新加载名为mysql的权限数据库

刷新数据库里的数据和日志文件(清除各种缓冲区或者临时缓存区);

查看MySQL服务器的工作状态和变量设置情况

1
2
mysqladmin -uroot -p -i 5 ping # printout 'mysqld is alive' per 5 seconds  
mysqladmin -uroot -p status # Uptime: 394 Threads: 1 Questions: 7 Slow queries: 0 Opens: 15 Flush tables: 1 Open tables: 8 Queries per second avg: 0.17

mysqladmin -uroot -p processlist
+—-+——+———–+—-+———+——+——-+——————+
| Id | User | Host | db | Command | Time | State | Info |
+—-+——+———–+—-+———+——+——-+——————+
| 8 | root | localhost | | Query | 0 | | show processlist |
+—-+——+———–+—-+———+——+——- +——————+

以上命令均可在MySQL本身无法进入的情况下进行查看,每个mysqladmin命令其实都有对应的mysql中的SQL命令,mysqladmin的另外一个场景是在自动化脚本中

列出和保存MySQL进程

测试与MySQL服务器的连接是否可用

关闭MySQL服务器

常规重启的命令

/etc/init.d/mysqld restart | stop,start # 通过 init.d/脚本管理

MySQL配置文件/etc/my.cnf

默认创建引擎设置

[mysqld]
default-storage-engine=INNODB # 将默认创建InnoDB引擎(而不是MyISAM)的数据表

MySQL通信设置

TCP/IP: 默认通过3306端口和TCP/IP协议通信,可以通过port选项来指定一个通信端口 skip-networking(等价于skip_networking in my.cnf)可以禁止MySQL通过TCP/IP协议通信,此时需要采取套接字文件通信
套接字文件(仅用于Linux/Unix),socket选项负责指出套接字文件的存放路径,必须同时出现在[mysqld]和[client]两个选项组里面.
命名管道或共享内存(windows)

服务器端字符集设置(与MySQL客户端通信时使用的字符集无关,客户端需要设置 jdbc://..default-character-set=utf8,或者通过命令SET NAMES ‘utf8’)

[mysqld]
character-set-server = latin1
collation-server = latin1_german1_ci

SQL模式的定义需要在 sql-mode选项中定义,影响较大,慎重选择.

版权声明

本文标题:14-mysql基本运维操作指南

文章作者:盛领

发布时间:2015年07月19日 - 18:31:44

原始链接:http://blog.xiaoyuyu.net/post/466a9f65.html

许可协议: 署名-非商业性使用-禁止演绎 4.0 国际 转载请保留原文链接及作者。

如您有任何商业合作或者授权方面的协商,请给我留言:sunsetxiao@126.com

盛领 wechat
欢迎您扫一扫上面的微信公众号,订阅我的博客!
坚持原创技术分享,您的支持将鼓励我继续创作!