mysq管理
mysql基本管理
1.mysql连接命令的基本使用
mysql命令
-u:用户名
-p:密码
-h:数据库ip地址
-P:数据库端口号
-S:mysql.sock文件路径
-e:表示可以在命令行下命名用mysql语句
例1：
mysql -uroot -poldboy
例2：
mysql -uroot -poldboy -h 10.0.0.52
例3：
mysql -uroot -poldboy -h 10.0.0.52 -P 3306
例3：
mysql -uroot -poldboy -S /application/mysql/tmp/mysql.sock
例4：
mysql -uroot -poldboy -e "show variables like 'server_id';"
2.mysql启动与关闭
2.1 mysql启动方式
#第一种
/etc/init.d/mysqld start
#第二种
/application/mysql/bin/mysqld_safe &
2.2 mysql的关闭
mysqladmin shutdown
[root@db02 ~]# mysqladmin -uroot -p123 shutdown
servive mysqld stop
[root@db02 ~]# /etc/init.d/mysqld stop
kill -9 ?
第三种为利用系统进程管理命令关闭MySQL。
kill pid #<==这里的pid为数据库服务对应的进程号。
killallmysqld #<==这里的mysqld是数据库服务对应的进程名字。
pkillmysqld #<==这里的mysqld是数据库服务对应的进程名字
2.3 mysql的启动参数设置优先级
1、预编译时候设置参数，参数会硬编码到程序中
2、命令行方式设定启动参数
3、初始化的配置文件
命令行最先优生效，初始化的配置文件第二优先，最后才是预编译时设置的参数生效
2.3 mysql的配置文件读取顺序
/etc/my.cnf ====> /etc/mysql/my.cnf ====> $MYSQL_HOME/my.cnf ====> defaults-extra-file ====> ~/.my.cnf
注：假设4个配置文件都存在，同时使用--defaults-extra-file指定了参数文件，如果这时有一个"参数变量"在5个配置文件中都出现了，那么后面的配置文件中的参数变量值会覆盖前面配置文件中的参数变量值，就是说会使用~/.my.cnf中设置的值
*****注意*****
如果使用./bin/mysqld_safe 守护进程启动mysql数据库时，使用了--defaults-file=<配置文件的绝对路径>参数，这时只会使用这个参数指定的配置文件。
2.3 mysql的初始化配置文件(/etc/my.cnf)
影响数据库的启动
[mysqld]
[mysqld_safe]
[server]:此模块包括前面两个模块
影响到数据库的链接
[mysql]
[mysqladmin]
[mysqldump]
[client]：此模块包含前面三个链接的模块
2.3.1 my.cnf配置文件案例
[mysqld]
basedir=/application/mysql
datadir=/application/mysql/data
socket=/application/mysql/tmp/mysql.sock
port=3306
server_id=10
log-error=/var/log/mysql.log
log-bin=/application/mysql/data/mysql-bin
binlog_format=row
skip_name_resolve
[mysql]
socket=/application/mysql/tmp/mysql.sock
3.mysql多实例配置
思路:
1、启动多个mysqld进程
2、规划多套数据
3、规划多个端口
4、规划多套日志路径
多实例配置
1、创建多套目录
mkdir -p /data/330{7,8,9}
2、准备多套配置文件
vi /data/3307/my.cnf
[mysqld]
basedir=/application/mysql
datadir=/data/3307
server-id=3307
port=3307
log-bin=/data/3307/mysql-bin
socket=/data/3307/mysql.sock
log-error=/data/3307/mysql.log
vim /data/3308/my.cnf
[mysqld]
basedir=/application/mysql
datadir=/data/3308
server-id=3308
port=3308
log-bin=/data/3308/mysql-bin
socket=/data/3308/mysql.sock
log-error=/data/3308/mysql.log
vim /data/3309/my.cnf
[mysqld]
basedir=/application/mysql
datadir=/data/3309
server-id=3309
port=3309
log-bin=/data/3309/mysql-bin
socket=/data/3309/mysql.sock
log-error=/data/3309/mysql.log
3、初始化多套数据
/application/mysql/scripts/mysql_install_db --basedir=/application/mysql/ --datadir=/data/3307 --user=mysql
/application/mysql/scripts/mysql_install_db --basedir=/application/mysql/ --datadir=/data/3308 --user=mysql
/application/mysql/scripts/mysql_install_db --basedir=/application/mysql/ --datadir=/data/3309 --user=mysql
4、启动多个实例
mysqld_safe --defaults-file=/data/3307/my.cnf &
mysqld_safe --defaults-file=/data/3308/my.cnf &
mysqld_safe --defaults-file=/data/3309/my.cnf &
5、查看启动端口
netstat -lnp |grep 330
6、分别连接测试
mysql -S /data/3307/mysql.sock -e "show variables like 'server_id';"
mysql -S /data/3308/mysql.sock -e "show variables like 'server_id';"
mysql -S /data/3309/mysql.sock -e "show variables like 'server_id';"
4.mysql用户管理
4.1 mysql用户定义
user:表示某个用户
host：表示主机范围(从哪个地址访问数据库)
password：表示用户密码
4.2 用户、权限、角色、权限范围
- 用户的作用：
1.用户登陆
2.用于管理数据库及数据 - 权限
针对不同用户设置对不同对象管理能力
对数据库的读、写操作(insert update select等) - 角色
对数据库定义好的一组权限定义(all privileges replication slave等) - 权限范围
全库级别：.
单库级别：oldboy.*
单表级别：oldboy.t1
grant all on wordpress.*(权限) to wordpress@"10.0.0.%"(权限范围) identified by "oldboy123"(用户密码)
4.3 用户权限操作
4.3.1 查看当前用户
mysql> select user,host,password from mysql.user;
4.3.2 创建用户
CREATE USER '用户'@'主机' IDENTIFIED BY '密码';
create user 'oldboy'@'localhost' identified by 'oldboy123';
#只有连接权限
企业里创建用户一般是授权一个内网网段登录，最常见的网段写法有两种。
方法1：172.16.1.%（%为通配符，匹配所有内容）。
方法2：172.16.1.0/255.255.255.0，但是不能使用172.16.1.0/24，是个小遗憾。
标准的建用户方法：
create user 'web'@'172.16.1.%' identified by 'web123';
4.3.3 查看用户对应的权限
mysql> show grants for test@localhost\G
4.3.4 删除用户
drop user 'user'@'主机域'
用户优化：只保留
| root | 127.0.0.1 |
| root | localhost|
特殊的删除方法:
mysql> delete from mysql.user where user='oldboy' and host='localhost';
Query OK, 1 row affected (0.00 sec)
mysql> flush privileges;
4.3.5 用户授权
如果对某个用户在不同数据库级别都设置了权限，最终权限相叠加，及加起来的最大权限为准，建议：尽量不要多范围授权。
4.3.5.1 给用户授权
GRANT ALL ON *.* TO 'oldboy'@'localhost';
4.3.5.2 查看用户权限
SHOW GRANTS FOR 'oldboy'@'localhost';
4.3.5.3 给用户添加权限
grant SELECT,INSERT, UPDATE, DELETE, CREATE, DROP on testdb.* to zabbix@'10.0.0.%';
4.3.5.4 创建用户的同时授权
grant all on *.* to oldgirl@'172.16.1.%' identified by 'oldgirl123';
flush privileges; #<==可以不用。
create user 'oldboy'@'localhost' identified by 'oldboy123';
GRANT ALL ON *.* TO 'oldboy'@'localhost';
4.3.5.5 授权和root一样的权限
grant all on *.* to 用户名@'localhost' identified by '密码' with grant option;
4.3.5.6 收回权限
收回单个权限
revoke select on testdb.* from zabbix@'10.0.0.%';
#收回所有权限
revoke all on testdb.* from zabbix@'10.0.0.%';
4.3.5.7 可以授权给用户的权限
ALL权限列表：
SELECT,INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES,INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE TABLESPACE
5.SQL字符集入门
- MySQL数据库的字符集：
字符集（CHARACTER）
校对规则（COLLATION） - MySQL中常见的字符集：
UTF8
LATIN1
GBK - 常见校对规则：
ci：大小写不敏感
cs或bin：大小写敏感 - 使用以下命令查看：
show charset;
show collation；
5.1 修改字符集
- 操作系统级别。
source /etc/sysconfig/i18n
[root@db02 logs]# echo $LANG
zh_CN.UTF-8 - 操作系统客户端级别（SSH）
- MySQL实例级别
方法1：在编译安装时候就指定如下服务器端字符集。
cmake.
-DDEFAULT_CHARSET=utf8 \
-DDEFAULT_COLLATION=utf8_general_ci \
-DWITH_EXTRA_CHARSETS=all \
方法2：
[mysqld]
character-set-server=utf8 -
获取帮助并查询
help create database;
show character set; -
MySQL客户端级别（连接及返回结果）
方法1：临时生效单条命令法。
mysql> set names utf8;
Query OK, 0 rows affected (0.00 sec)
方法2：通过修改my.cnf实现修改mysql客户端的字符集，配置方法如下。
[client]
default-character-set=utf8 -
程序代码级别
生产环境更改数据库（含数据）字符集的方法
alter database oldboy CHARACTER SET utf8 collate utf8_general_ci;
alter table t1 CHARACTER SET latin1;
ps:注意：更改字符集时，一定要保证由小往大改，后者必须是前者的严格超集。
生产中别随便改。
<wiz_tmp_tag id="wiz-table-range-border" contenteditable="false" style="display: none;">
