Mysql DBA 高级运维学习之路-Mysql常用基础命令实战

arongsoft

7.1 单实例mysql启动和关闭方法
　　1.常规方法启动数据库
　　（1）启动mysql服务命令
　　

[root@localhost ~]# /etc/init.d/mysqld start　　
Starting MySQL. SUCCESS!
　　

　　（2）查看mysql端口
　　

[root@localhost ~]# ss -lnt|grep 3306　　
LISTEN 0  50*:3306 *:*
　　

　　（3）查看mysql进程
　　会启动两个进程第一个就是mysql_safe第二个是mysqld
　　

[root@localhost ~]# ps -ef|grep mysql|grep -v grep　　
root   2796  1  0 16:23 pts/000:00:00 /bin/sh /usr/local/mysql/bin/mysqld_safe --datadir=/usr/local/mysql/var --pid-file=/usr/local/mysql/var/localhost.localdomain.pid
　　
mysql  2912   2796  0 16:23 pts/000:00:00 /usr/local/mysql/libexec/mysqld --basedir=/usr/local/mysql --datadir=/usr/local/mysql/var --user=mysql --log-error=/usr/local/mysql/var/localhost.localdomain.err --pid-file=/usr/local/mysql/var/localhost.localdomain.pid --socket=/tmp/mysql.sock --port=3306
　　

　　（4）MySQL数据库启动原理
　　/etc/init.d/mysqld 是一个shell启动脚本，启动后最终会调用mysqld_safe脚本，最后调用mysqld服务启动mysql。
　　如下，/etc/init.d/mysqld脚本中调用的mysqld_safe的程序。
　　

$bindir/mysqld_safe --datadir=$datadir --pid-file=$server_pid_file $other_args >/dev/null 2>&1 &　　

　　2.初始化数据库时MySQL系统输出的给出的启动方法
　　Mysqld_safe --user=mysql &
　　提示：
　　(1) 当找回root密码时，会经常使用mysqld_safe –user=mysql &带参数启动
　　(2)我们自己开发数据库启动脚本时，会用到这个方法。
　　(3)/etc/init.d/mysqld和mysqld_safe --user=mysql的启动实质一样。一般出故障的时候我们用mysqld_safe来启动，因为可以加参数。
　　

[root@localhost ~]# cd /usr/local/mysql/|mysqld_safe &　　

　　3.常规方法关闭数据库
　　(1) 关闭MySQL命令
　　

[root@localhost ~]# /etc/init.d/mysqld stop　　

　　
Shutting down MySQL. SUCCESS!
　　

　　
[root@localhost ~]# ss -lnt|grep 3306
　　

　　(2) MySQL常规关闭数据库原理
　　

'stop')　　
# Stop daemon. We use a signal here to avoid having to know the
　　
# root password.
　　
# The RedHat / SuSE lock directory to remove
　　
lock_dir=/var/lock/subsys/mysqlmanager
　　
# If the manager pid_file doesn't exist, try the server's
　　
if test ! -s "$pid_file"
　　
then
　　pid_file=$server_pid_file
　　lock_dir=/var/lock/subsys/mysql
　　
fi
　　
if test -s "$pid_file"
　　
then
　　mysqlmanager_pid=`cat $pid_file`
　　if (kill -0 $mysqlmanager_pid 2>/dev/null)
　　then
　　
echo $echo_n "Shutting down MySQL"
　　
kill $mysqlmanager_pid
　　
# mysqlmanager should remove the pid_file when it exits, so wait for it.
　　
wait_for_pid removed "$mysqlmanager_pid"; return_value=$?
　　else
　　
log_failure_msg "MySQL manager or server process #$mysqlmanager_pid is not running!"
　　
rm $pid_file
　　fi
　　

　　（4）强制关闭mysql
　　Killall mysqld
　　Pkill mysqld
　　Killall -9 mysqld
　　Kill pid
　　提示：第二种方法启动和关闭数据库一般生产环境不用，特别是关闭命令。
　　强调：尽量不要野蛮杀死mysql服务，生产高并发环境可能会引起数据丢失。
　　下面引用老男孩老师的博文，野蛮杀死数据库导致的故障企业案例：
　　http://oldboy.blog.51cto.com/2561410/1431161
　　http://oldboy.blog.51cto.com/2561410/1431172
　　http://www.cnblogs.com/peida/archive/2012/12/20/2825837.html
　　(5) 优雅关闭数据库方法
　　第一种mysqladmin方法
　　[root@localhost ~]# mysqladmin -uroot -p123456 shutdown
　　第二种方法自带的脚本
　　/etc/init.d/mysqld stop
　　4.多实例mysql启动与关闭方法实例
　　启动：
　　

/data/3306/mysql start　　
/data/3307/mysql start
　　

　　关闭：
　　

/data/3306/mysql stop　　
/data/3306/mysql stop
　　

　　多实例启动脚本实现启动和关闭方法
　　启动：
　　

${cmdPath}/mysqld_safe --defaults-file=${myPath}/my.cnf --user=mysql --basedir=${softPath} --datadir=${myPath}/data &>/dev/null &　　

　　关闭：
　　

mysqladmin -u"${my_user}" -p"${my_pass}" -S "$socketfile" shutdown　　

7.2 登录mysql方法

7.2.1 单实例mysql登录
　　① Mysql                    刚装完数据库无需密码登录，不要密码。
　　② Mysql –uroot             刚装完数据库无需密码登录，不要密码。
　　③ Mysql –uroot –p          这是标准的命令行登录命令。
　　④ Mysql –uroot –p’123456’  非脚本一般不这样用，密码明文会泄露密码可以掩饰history功能解决。
　　解决方法：
　　History –c清空历史记录
　　History –d 删除指定行
　　可以查看系统历史记录，可以删除
　　

[root@mysql 3306]# cat /root/.bash_history　　

　　也可以查看mysql历史记录，可以删除
　　

[root@mysql 3306]# cat /root/.mysql_history　　

　　登录后默认提示符是mysql>这个提示符可以修改，目的是为了区分测试环境和正式环境。工作中一定要先区分正式环境和测试环境。不管正式环境还是测试环境都要先备份在操作。
　　更改mysql数据登录提示符（了解的知识）方法如下：
　　(1) 命令行修改提示符
　　

mysql> prompt \u@zhengshi \r:\m\s-> 这种改变是临时的不生效的　　

　　(2) 配置文件修改提示符
　　在my.cnf配置文件中的[mysql]模块下添加如下内容，保存后，无需重启mysql，退出当前session重新登录即可，如果在配置文件中添加，可以用\避免转义带来的问题。
　　

[mysql]　　
no-auto-rehash
　　
prompt \\u@ceshi \\r:\\m\\s->
　　

7.2.2多实例mysql登录方法
　　多实例mysql本地登录
　　

[root@mysql ~]# mysql -uroot -p -S /data/3306/mysql.sock　　
[root@mysql ~]# mysql -uroot -p -S /data/3307/mysql.sock
　　

　　提示：多实例通过mysql的-S 指定不同的sock文件登录
　　注意：多实例远程连接无需指定sock路径
　　

mysql -uroot -p -h 192.168.1.115 -P3306　　

7.2.3 善用mysql帮助命令help
　　MySQL中的help命令和linux命令行的man是类似的，想要查看MySQL中的命令使用语法，就到需要用help，help后面接相关命令及命令组合即可。例如：help create.
　　root@ceshi 09:4813->help

7.3 设置及修改mysql root用户密码

7.3.1 MySQL数据库用户安全策略介绍
　　安装完mysql数据库后，默认的管理员root密码为空，很不安全，因此要设置一个密码，在安装MySQL单实例后，我们已经做了一些安全措施，例如：
　　a.  为root设置密码
　　b.  删除无用的mysql库内的用户账号
　　c.  删除默认存在的test数据库。
　　除了上面的方法，针对MySQL数据库的用户处理，我们还需要删除root添加新的管理员用户。
　　（1）删除所有mysql中的用户，包括root超级用户。
　　

root@ceshi 10:5249->delete from mysql.user;　　
Query OK, 0 rows affected (0.00 sec)
　　

　　（2）增加system并升级为超级管理员，及和root等价的用户。
　　

grant all privileges on *.* to 'system'@'localhost'>　　

7.3.2 为管理员设置密码
　　

mysqladmin -u root password 'zbf666' 没有密码的用户设置密码。　　
mysqladmin -u root -S /data/3307/mysql.sock password '123456' 多实例设置密码。
　　

　　(1)命令行外修改管理员root密码
　　

mysqladmin -usystem -p123456 password 'zbf666'　　
mysqladmin -usystem -p123456 password 'zbf666' –S /data/3306/mysql.sock 适合多实例
　　

　　(2)Sql语句修改管理员密码
　　

system@ceshi 11:4303->update mysql.user set password=password("wwn520") where user='system' and host='localhost';　　
Query OK, 1 row affected (0.00 sec)
　　
Rows matched: 1  Changed: 1  Warnings: 0
　　
system@ceshi 11:4321->flush privileges;刷新到数据文件
　　
Query OK, 0 rows affected (0.00 sec)
　　

　　提示：
　　1.必须指定where条件。
　　2.必须使用password()函数来加密更改密码。
　　注意：如果是生产环境，应该起码8为数字并且有字母数字的混合。
　　这种方法可以使用—skip-grant-tables找回密码。
　　(3)第三个方法修改管理员密码
　　很少用这种方法
　　

system@ceshi 11:4358->set password=password("zbf666");　　
Query OK, 0 rows affected (0.00 sec)
　　
system@ceshi 11:4845->flush privileges;
　　
Query OK, 0 rows affected (0.00 sec)
　　

7.4   找回丢失的mysql root用户密码

7.4.1 启动修改丢失的Mysql单实例root密码方法

• 　　首先停止数据库
  　　
  
  [root@localhost ~]# /etc/init.d/mysqld stop　　
  Shutting down MySQL. SUCCESS!
  　　
  
  
  
• 　　带--skip-grant-tables启动mysql
  　　
  
  [root@localhost ~]# mysqld_safe --skip-grant-tables --user=mysql &　　
  
  　　
  [root@localhost ~]#Mysql 登录时空密码
  　　
  
  
  
• 　　无密码即可登录mysql
  　　
  
  [root@localhost ~]# mysql　　
  
  　　
  Welcome to the MySQL monitor.  Commands end with ; or \g.
  
  　　
  Your MySQL connection>　　
  Server version: 5.1.72-log Source distribution
  　　
  Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.
  　　
  Oracle is a registered trademark of Oracle Corporation and/or its
  　　
  affiliates. Other names may be trademarks of their respective
  　　
  owners.
  　　
  Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
  　　
  root@ceshi 12:3556->
  　　
  
  
  
• 　　修改root密码为新密码
  　　
  
  root@ceshi 12:3838->set password=password("zbf666");　　
  
  　　
  ERROR 1290 (HY000): The MySQL server is running with the --skip-grant-tables option so it cannot execute this statement
  　　
  
  　　
  root@ceshi 12:3913->update mysql.user set password=password("zbf666") where user='system' and host='l='localhost';
  　　
  
  　　
  Query OK, 1 row affected (0.00 sec)
  　　
  
  　　
  Rows matched: 1  Changed: 1  Warnings: 0
  　　
  
  　　
  root@ceshi 12:3936->flush privileges;
  　　
  
  　　
  Query OK, 0 rows affected (0.00 sec)
  　　
  
  
  
• 　　重启服务在登录
  　　
  
  [root@localhost ~]# mysqladmin -usystem -pzbf666 shutdown;　　
  
  　　
  180118 00:42:53 mysqld_safe mysqld from pid file /usr/local/mysql/var/localhost.localdomain.pid ended
  　　
  [1]+  Donemysqld_safe --skip-grant-tables --user=mysql
  　　
  
  　　
  [root@localhost ~]# /etc/init.d/mysql start
  　　
  
  　　
  [root@localhost ~]# mysql -usystem -pzbf666
  　　
  
  　　
  Welcome to the MySQL monitor.  Commands end with ; or \g.
  
  　　
  Your MySQL connection>　　
  Server version: 5.1.72-log Source distribution
  　　
  Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.
  　　
  Oracle is a registered trademark of Oracle Corporation and/or its
  　　
  affiliates. Other names may be trademarks of their respective
  　　
  owners.
  　　
  Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
  　　
  system@ceshi 12:4329->
  　　
  
  
  

7.4.2 多实例MySQL启动修改丢失root密码
　　1.关闭mysql
　　Killall mysqld
　　2.启动时加--skip-grant-table参数，指定3306的配置文件
　　

[root@mysql ~]#mysqld_safe --defaults-file=/data/3308/my.cnf --skip-grant-table &　　
[1] 10993
　　
[root@mysql ~]#180205 03:54:09 mysqld_safe Logging to '/data/3308/mysql_zbf3308.err'.
　　

　　
180205 03:54:09 mysqld_safe Starting mysqld daemon with databases from /data/3308/data
　　

　　
[root@mysql ~]# mysql -u root -p -S /data/3308/mysql.sock 登录时空密码
　　

　　
Enter password:
　　
Welcome to the MySQL monitor.  Commands end with ; or \g.

　　
Your MySQL connection>　　
Server version: 5.5.32-log Source distribution
　　

　　
Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.
　　

　　
Oracle is a registered trademark of Oracle Corporation and/or its
　　
affiliates. Other names may be trademarks of their respective
　　
owners.
　　

　　
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
　　

　　3.登录数据库
　　

Mysql –u root –p –S /data/3306/mysql.sock 登录时空密码　　

　　4.修改数据库密码
　　

mysql> update mysql.user set password=password("zbf") where user='root';　　

　　
Query OK, 5 rows affected (0.00 sec)
　　
Rows matched: 5  Changed: 5  Warnings: 0
　　

　　
mysql> flush privileges;
　　

　　
Query OK, 0 rows affected (0.00 sec)
　　

　　5.重启服务用新密码登录
　　Killall mysqd
　　单实例：/etc/init.d/mysqld start
　　多实例：/data/3306/mysql start