MySQL数据库日常运维管理命令集

一、MySQL启动

1.查看该版本相应的参数
mysqld --verbose --help
mysqld --verbose --help|grep my.cnf

2.启动MySQL的方式

1)/path/support-file/mysql.server start    #用二进制、rpm包、源码安装、yum源安装，root用户下启动
cp /apps/conf/mysql/mysql5_3306.cnf /etc/my.cnf #要把参数拷到默认的位置

2)/etc/init.d/mysqld start      #root用户下启动                                                                                                                                                                                                                                                                                                                                                                    #如果是使用二进制安装，mysqld来自mysql目录的support-file/mysql.server，
可以拷贝cp support-file/mysql.server /etc/init.d/mysqld
ls /etc/init.d/msyql*

3)service mysqld start            #用mysql rpm安装
#先调用mysqld_safe,再调用mysqld

4)mysqld --defaults-file=/u01/my3306/my.cnf &
#mysqld是MySQL的一个核心程序，用于管理MySQL的数据库文件及用户的请求操作，同时也是参数最多的，
mysqld是可以读取配置文件中的[mysqld]部分

5)mysqld_safe --defaults-file=/u01/my3306/my.cnf &
#mysqld_safe调用mysqld(Linux推荐的)， 并对其进行监视。如果 mysqld 异常终止，
mysqld_safe 会将其重新启动。指定读取配置文件，不在读其它配置文件。

6)2种方式启动多实例！！！！
第一种：mysqld_safe --defaults-file=/u01/my3306/my3306.cnf &
第二种：/u01/my3306/bin/mysqld_multi  start 3306   #
mysqld_multi report
mysqld_multi start
#多用于管理多实例启动的一个脚本，可以调用mysqld_safe→mysqld或直接调用mysqld，这个可以配置
该 Perl 脚本用于简化单台主机上的多个服务器管理。它可以启动或停止服务器，它还可以报告服务器是否正在运行。

注意把参数放在/etc/my.cnf下面，并且在参数文件中多加：#root用户启
vim /etc/my.cnf

[mysqld_multi]
mysqld=/u01/svr/mysql5.6/bin/mysqld_safe
mysqladmin=/u01/svr/mysql5.6/bin/mysqladmin
user=root
log=/u01/logs/multi.log

[mysqld3306]
port = 3306
datadir = /u01/mydata/my3306/
socket = /tmp/my3306.sock
server_id = 20032326
log-bin=/u01/logs/my3306/binlog/mysql-bin
log-error=/u01/logs/my3306/error3306.log
slow_query_log_file=/u01/logs/my3306/slow3306.log
innodb_data_home_dir=/u01/logs/my3306/iblog
innodb_log_group_home_dir=/u01/logs/my3306/iblog

[mysqld3307]
port = 3307
datadir = /u01/mydata/my3307/
socket = /tmp/my3307.sock
server_id = 20032327
log-bin=/u01/logs/my3307/binlog/mysql-bin
log-error=/u01/logs/my3307/error3307.log
slow_query_log_file=/u01/logs/my3307/slow3307.log
innodb_data_home_dir=/u01/logs/my3307/iblog
innodb_log_group_home_dir=/u01/logs/my3307/iblog
relay_log=/u01/logs/my3307/relaylog/relay-log
relay_log_index=/u01/logs/my3307/relaylog/relay.index
relay_log_info_file=/u01/logs/my3307/relaylog/relay-log.info

★Note
ps -ef |grep mysqld                #是否启动
tail  -f /u01/my3306/log/error.log #启动报错
--defaults-exta-file                #指定读取配置文件后，还需要读取用户指定的特殊的配置文件
--print-defaults                    #输出现在mysqld的指定参数，mysqld --print-defaults
[mysqld3306] [mysqld]              #同一个参数同时放在[mysqld3306]和[mysqld]，[mysqld3306]覆盖[mysqld]
[mysqld_safe]                      #参数的优先级是最高的，会覆盖[mysqld]的中部分

./scripts/mysql_install_db  --defaults-file=/u01/my3308/my.cnf --datadir=/u01/my3308/data --user=mysql --skip-name-resolve  --force
netstat -nalp |grep mysql

-----------------------------------------------------------------------------------
安装二进制mysql
下载： mysql-5.6.35-linux-glibc2.5-x86_64.tar.gz
tar -xzvf mysql-5.6.35-linux-glibc2.5-x86_64.tar.gz
cd /usr/local
ln -s /u01/mysql-5.6.35-linux-glibc2.5-x86_64 mysql
vim /etc/profile
export PATH=$PATH:/usr/local/mysql/bin
source /etc/profile
cat /etc/profile|grep "PATH"
echo $PATH
vim /etc/my.cn
basedir=/usr/local/mysql
datadir=/data/mysql/mysql_3306

cd /data/mysql/
mkdir mysql_3306
mkdir mysql_3306/data
mkdir mysql_3306/log
mkdir mysql_3306/tmp
初始化数据库：
./scripts/mysql_install_db  --defaults-file=/etc/my.cnf --datadir=/data/mysql/mysql_3306
cp support-file/mysql.server /etc/init.d/mysqld
ls /etc/init.d/msyql*
--------------------------------------------------------------------------------------------

二、MySQL关闭
1.MySQL关闭流程
1)MySQL接到single 0的信号
2)清场处理，关闭所有的连接，刷新内存里的数据
3)关闭

2.关闭MySQL的方式
1)service mysqld stop
2)/etc/init.d/mysqld stop
3)mysqladmin -S  /u01/my3306/run/mysql.sock shutdown &
4)kill -9 pid

要手动停止服务器，请使用以下方法之一：
mysqladmin：具有关闭命令。它作为客户机连接到服务器并且可以关闭本地或远程服务器。
mysql.server：在使用 stop 参数调用时停止和/或关闭本地服务器
mysqld_multi：停止和/或关闭其管理的任何服务器。它通过调用 mysqladmin 来执行此操作
mysqld_safe 没有服务器关闭功能。可以转而使用 mysqladmin shutdown。请注意，
如果您通过使用 kill -9 命令向 mysqld 发送信号来将其强制终止，则 mysqld_safe 会检测到 mysqld
异常终止并将其重新启动。可以通过先终止 mysqld_safe 再终止 mysqld 来解决此问题，但是最好使用
mysqladmin shutdown，这会启动正常（干净）服务器关闭。

3)常用的启动关闭
mysqld_safe   --defaults-file=/u01/my3306/my.cnf &
mysqladmin    -S  /u01/my3306/run/mysql.sock shutdown &

三、登陆MySQL
1.本地
mysql –u$username –p$password

2.远程
mysql –u$username –p$password –h $ip

3.多实例
mysql –u$username –p$password –P $port

四、账户权限设置

select Host,User,Password,Select_priv,Grant_priv from user;
1.创建用户
1)方法一：
insert into mysql.user(user,host,password) values('mytest','localhost',password('1234'));
flush privilege;

#为什么insert要刷下缓存，flush privilege只是针对mysql.user的表。insert直接把记录插入到mysql.user这个表
做持久化了，缓存是不知道的，用flush privilege命令把缓存重新刷下把mysql.user表的最新数据load到缓存中(因mysql.user表数据量不大)。


2)方法二：create user mystest@'%' identified by '1234';
show grants for mystest@'%';

2.用户授权
1)单纯的授权
grant insert,update,delete,select on jfedu.* to 'jfedu'@'192.%';
#grant的做了2件事情，：插入记录并load缓存

2)授权并创建用户
grant insert,update,delete,select on jfedu.* to 'jfedu'@'192.%' identified by '1234'
#创建用户并刷缓存，（等同于:insert into mysql.user ,flush privilege）
grant all privileges on *.* to mytest@localhost;  #对象权限
grant super on *.* to mytest@'%';   #系统权限  (supert相当于oracle中的dba权限)
grant usage,select, insert, update, delete, create, alter, index, drop ON vip_crawler.* TO 'uvpalreport'@'10.%';

4.用户权限级别
1)核心开发权限(一般给增删改查)
select/insert/update/delete

2)管理权限-表级别
create/drop/lock tables/file

3)管理权限--server级别
grant option/create tablespace/create user/process/proxy/reload/replication client
replication slave/show databases/shutdown/super/all priveleges/usage


grant select,update,insert,delete on mydba.* to 'mydba'@'192.0%' identified by  'mydba';


4.删除用户
drop user '用户名'@'来源';
如果没有指定来源，会把这个用户名的所有账号删除
用户被删除，权限还在？

五、MySQL数据库安全配置
1.禁用多余的管理员账号
1)查询账户(MySQL用户存储在表)
select user,host,password from mysql.user;
2)删除账户
delete from mysql.user where user !='root' or host !='localhost';
3)刷到磁盘
flush privileges;
4)查询账户
select user,host,password from mysql.user;

2.删除掉db表数据(和DB里权限相关的表):从任何地方(用了一些通配符)连接上来的用户都可以访问TEST库,为了安全可以删除掉db表数据。
1)登录到mysql库
use mysql;
2)查表db
show tables;
select * from db;
3)删除db表数据
truncate table db;
4)刷到磁盘
flush privileges;
5)检查db表
select * from db;

3.删除test库   #删除MySQL默认安装的测试数据库test，防止测试数据库被攻击者利用
select database();
drop database test;

4.修改管理员账户名  #MySQL默认账号名为root，该用户拥有对所有数据库的完全访问权，修改账号名防止管理员密码被穷举。
select user();
use mysql;
UPDATE user SET user="jfedudbroot" WHERE user="root";
flush privileges;

5.密码复杂度要求
update user set password=password('JfeduDB@com123') where  user='jfedudbroot';
flush privileges;

6.权限最小化
配置步骤参考：使用GRANT命令为用户授权，只授予用户必要的权限，禁止授予非必要的权限。
使用REVOKE 命令为用户回收不必要权限。
查看数据库授权情况：
use mysql;
select * from user;
select * from db;
select * from tables_priv;
select * from columns_priv;
show grants for 'jfedu'@'192.%';

六、表操作--线上可以直接删除表吗?

drop table gyj_t1;

检查表是否还在被访问?    show processliset;?
重命名临时表?          rename table test_1 to test;(可以快速切回来rename table test to test_1;)
备份(物理备份或者逻辑备份)?mysqldump -h127.0.0.1 -uroot mydb gyj_t1 >/tmp/gyj_t1.sql
删除临时表?           drop table  test;

1.show tables;
2.show processlist;
3.rename table gyj_t10 to gyj_t10_bak;
4.mysqldump -h127.0.0.1 -uroot jfedu gyj_t10_bak > /tmp/gyj_t10_bak20170125.sql
5.drop table gyj_t10_bak;
6.show tables from jfedu like '%gyj%';


七、如何在线迁移MySQL
1)物理上：搭备库（可以级联5.5-->5.6，向下兼容的）
2)把主库read only,备库就能把主库转过来的binlog消化完，再把备库切为主
3)show variables like '%read%';
4)set global read_only=on;
5)insert into test(name) values('xx');  --插不进的，不能用root用户

1.服务器A上创建一个复制账号
GRANT REPLICATION SLAVE,REPLICATION CLIENT ON *.* TO 'repl'@'%' IDENTIFIED BY 'repl4Slave';
#create database jfedu default character set utf8;

2.服务器A和服务器B上分别设置不同的server_id
show variables like '%server_id%';

3.服务器A执行一次完整的逻辑备份
mysqldump --single-transaction --master-data=2 -uroot -p'GAre79@#$gtyu$523e##443hg' jfedu > /tmp/jfedu20170205.sql
grep -i "CHANGE MASTER TO" /tmp/jfedu20170205.sql

#--master-data=2 是把change log 那行注释掉，=1 是没有注释.
#mysqldump --single-transaction --master-data=1/2  --flush-logs/-F

4.从服务器A拷贝备份到服务器B
scp /tmp/jfedu20170205.sql

5.服务器B上执行一次全量恢复
source /tmp/jfedu20170205.sql

6.服务器B上执行change master设置主从复制
change master to
master_host='10.203.30.185',
master_port=3306,
master_user='repl',
master_password='repl4Slave',
master_log_file='mysql-bin.000003',
master_log_pos=107;

7.服务器B上执行start slave启动复制
start slave;
start slave io_thread;
start slave sql_thread;

8.服务器A上设为read only
set global read_only=on;

9.服务器B设为主库(服务器B把服务器A转过来的binlog消化完)
show databases;
use jfedu;
show tables;
select * from gyj_t1;


八、MySQL升降级
1.MySQL升级
5.6升级5.7,
5.7降级5.6

master 5.6 ----> slave 5.6  #线上
|
|
slave 5.7---->master 5.7
|
|
slave 5.7


2.升级操作步骤：
1)关闭mysql 5.6

2)替换mysql二进制软件
unlink mysql
ln -s  mysql5.7.8-rc-linux-glibc2.5-x86_64/ mysql

2)启动mysql 5.6
如果报错看错误日志。可能目录有问题？

3)备份下mysql 5.6系统库
cp -rf mysql mysql_5.6

4)升级数据字典
mysql_upgrade -s

3.降级操步骤：
1）关闭mysql5.7
2)替换mysql二进制软件
unlink mysql
ln -s  mysql5.6.35-rc-linux-glibc2.5-x86_64/ mysql
3)还原下5.6的系统库
cp -rf  mysql_5.6 mysql


九、MySQL常用命令
show databases;   --查看所有的数据库
use mysql;        --切到mysql数据库
show tables;      --查mysql库的tables
select user,host,password from mysql.user;  ----查mysql的所有用户，这个是由mysql_install_db创建的
grant all privilege on *.* to test_1@'%'; --all代表(select update,delete,alter admin,super_acl),第一个*用户，第二个*对象，%所有的主机
mysql -h127.0.0.1 -utest_1    ----用grant创建的用户登录mysql
select user();   ---当前是什么用户
create database jianfeng  charect utf8; ---创建数据库（mysql中的数据库类似于oracle中的schema
create table user(id int) engine=innodb   ---创建表;
grant select on jianfeng.user to test_1@'%';  ---jianfeng.user表的查询授权给test_1用户
insert into mysql.user(user,host,password) values('test_2','%',password('1234')); --用这种方法创建test_2用户，有个问题权限没有
flush privileges;  ---把mysql.user表的用户权限重新刷到内存中
flush logs;       #直接切到另一个日志，binlog是不重要的，所以他是归档
show master status\G;
change master to xxx;
show processlist;   ---查看当前用户的连接,线程形式（类似oracle中的v$session），select * from tables t1,tables t2 tables t3;
show engine innodb status\G
show tables from information_schema like 'INNODB%';
show table status like '%t1%';
show status\G;
show grants for gyj@'%';
show global status like '%insert%';

________________________________________权限________________________________________

CREATE DATABASE vip_finance DEFAULT CHARACTER SET utf8;
GRANT USAGE,SELECT, INSERT, UPDATE, DELETE ON vip_finance_instmt.* TO 'finance'@'10.%' IDENTIFIED BY 'wKib0k3cdsEwaa1G';
flush privileges;

________________________________________统计信息________________________________________
show global status like '%insert%';
sleep 1
show global status like '%insert%';
print 2-1; insert/s
show global status like '%update%';
show global status like '%delete%';

________________________________________root密码修改________________________________________
方法1： 用SET PASSWORD命令

mysql -u root
mysql> SET PASSWORD FOR 'root'@'localhost' = PASSWORD('root123');

方法2：用mysqladmin

mysqladmin -u root password "newpass"

如果root已经设置过密码，采用如下方法

mysqladmin -u root password oldpass "newpass"

方法3： 用UPDATE直接编辑user表

mysql -u root
mysql> use mysql;
mysql> UPDATE user SET Password = PASSWORD('newpass') WHERE user = 'root';
mysql> FLUSH PRIVILEGES;

在丢失root密码的时候，可以这样

mysqld_safe --skip-grant-tables&
mysql -u root mysql
mysql> UPDATE user SET password=PASSWORD("new password") WHERE user='root';
mysql> FLUSH PRIVILEGES;


___________________________________________字符集___________________________________________
select  error_msg,length(error_msg),char_length(error_msg) from pub_error where error_msg!='';
show create table pub_error\G



___________________________________________其它___________________________________________
#查看进程下的所有线程
pstack 9912（mysqld spid）

#查表的结构
mysqlfrm --diagnostic  time_zone_name.frm

1）安装
tar -xzvf mysql-utilities-1.6.5.tar.gz
python ./setup.py build
python ./setup.py install

#查看my.cnf的启动顺序
mysqld --verbose --help|grep my.cnf

create database ocp default charecter set utf8;
GRANT USAGE,SELECT, INSERT, UPDATE, DELETE ON ocp.* TO 'ocp'@'%' IDENTIFIED BY 'ocp';
GRANT CREATE,DROP,ALTER ON ocp.* TO 'ocp'@'%';


show variables like 'innodb_data_file_path'\G
一个pagesize:16K
一行16byte

16*1024/=1024行




多实例：
1、添加3307参数（3306-->3307）

2、建目录：数据目录、日志目录

mkdir -p /apps/dbdat/mysql5_data3307/log

3、初始化数据库
./scripts/mysql_install_db  --defaults-file=/apps/conf/mysql/mysql5_3307.cnf --datadir=/apps/dbdat/mysql5_data3307 --user=apps

4、启动实例
mysqld_safe --defaults-file=/apps/conf/mysql/mysql5_3307.cnf --user=apps &