MySQL DBA常用小知识有哪些

共计 5418 个字符，预计需要花费 14 分钟才能阅读完成。

这期内容当中丸趣 TV 小编将会给大家带来有关 MySQL DBA 常用小知识有哪些，文章内容丰富且以专业的角度为大家分析和叙述，阅读完这篇文章希望大家可以有所收获。

1.mysql 的远程连接命令可以远程导入导出数据

mysqldump –default-character-set=gb2312 -h355.255.000.00 -uroot -pxxxxxx mydatabase d:\data.sql

将指定的数据库导出到一个外部 SQL 文件中去！

还原命令：

mysql –default-character-set=gb2312 -h355.255.000.00 -uroot -pxxxxxx mydatabase d:\data.sql

2. MYSQL 三种升级方法

安装办法：二进制包编译安装

第一种：适用于任何一种存储引擎。

1. 并安装好新版本的 MySQL 数据库，并将其端口改为 3307（避免和旧版本的 3306 冲突），启动服务。

2. 在新版本下创建同名数据库。

# mysqldump  -p3307  -uroot  create mysqlsystems_com

3. 在旧版本下备份该数据库。

# mysqldump  -p3306  -uroot  mysqlsystems_com mysqlsystems_com.bk

Note: 你也可以加上 ndash;opt 选项，这样可以使用优化方式将你的数据库导出，减少未知的问题。

4. 将导出的数据库备份导入到新版本的 MySQL 数据库中。

# mysql -p3307 -uroot mysqlsystems_com mysqlsystems_com.bk

5. 再将旧版本数据库中的 data 目录下的 mysql 数据库全部覆盖到新版本中。

# cp -R /opt/mysql-5.1/data/mysql  /opt/mysql-5.4/data（权限库保持不变）

Note: 大家也都知道这个默认数据库的重要性。

6. 在新版下执行 mysql_upgrade 命令，其实这个命令包含一下三个命令：

# mysqlcheck ndash;check-upgrade ndash;all-databases ndash;auto-repair

# mysql_fix_privilege_tables

# mysqlcheck ndash;all-databases ndash;check-upgrade ndash;fix-db-names ndash;fix-table-names

Note: 在每一次的升级过程中，mysql_upgrade 这个命令我们都应该去执行，它通过 mysqlcheck 命令帮我们去检查表是否兼容新版本的数据库同时 作出修复，还有个很重要的作用就是使用 mysql_fix_privilege_tables 命令去升级权限表。

7. 关闭旧版本，将新版的数据库的使用端口改为 3306，重新启动新版本 MySQL 数据库。到此，一个简单环境下的数据库升级就结束了。

第二种，同样适用任何存储引擎。

1. 同样先安装好新版本的 MySQL。

2. 在旧版本中，备份数据库。

# mkdir /opt/mysqlsystems_bk  ;  mysqldump -p3306 -uroot ndash;tab=/opt/mysqlsystems_bk  mysqlsystems_com

Note: ndash;tab 选项可以在备份目录 mysqlsystems_bk 下生成后缀为 *.sql 和 *.txt 的两类文件；其中，.sql 保存了创建表的 SQL 语句而.txt 保存着原始数据。

3. 接下来在新版本的数据库下更新数据。

# mysqladmin -p3307 -uroot create mysqlsystems_com

# cat /opt/mysqlsystems_bk/*.sql | mysql -p3307 -uroot  mysqlsystems_com  (Create Tables)

# mysqlimport mysqlsystems_com /opt/mysqlsystems_bk/*.txt  (Load Data)

4. 之后的所有步骤与第一种方法的后三步 5、6、7 相同。

第三种，适用于 MyISAM 存储引擎，全部是文件间的拷贝。

1. 安装。

2. 从旧版本 mysqlsystems_com 数据库下将所有.frm、.MYD 和.MYI 文件拷贝到新版本的相同目录下。

3. 之后的步骤依然同于第一种的后三步。

以上就是三种升级 MySQL 的方法，看似没有出现什么问题，其实，在实际的生产环境中，为会有诸多问题发生，这就需要我们在升级之前充分了解新版本中增加了哪些新功能，进一步分析升级以后这些新特性是否将会对我们原来应用产生影响。

3. MYSQL 远程连接不了的解决方案

有可能是这个用户权限不够。查看一下权限表。

4. MYSQL 忘记密码解决办法

在 windows 下：

打开命令行窗口，停止 mysql 服务：Net stop mysql

到 mysql 的安装路径启动 mysql，在 bin 目录下使用 mysqld-nt.exe 启动，在命令行窗口执行：mysqld-nt –skip-grant-tables

然后另外打开一个命入令行窗口，执行 mysql，此时无需输入密码即可进入。

use mysql

update user set password=password(new_pass) where user= root

flush privileges;

exit

使用任务管理器，找到 mysqld-nt 的进程，结束进程！

在重新启动 mysql-nt 服务，就可以用新密码登录了。

在 linux 下：

如果 MySQL 正在运行，首先杀之：killall -TERM mysqld。

启动 MySQL：bin/safe_mysqld –skip-grant-tables

就可以不需要密码就进入 MySQL 了。

然后就是

use mysql

update user set password=password(new_pass) where user= root

flush privileges;

重新杀 MySQL，用正常方法启动 MySQL。

5. 更改 MYSQL 的默认字符集

法 1、用 SET 语法来指定，不加 GLOBAL 的话就只对本次会话有效

SET [GLOBAL] character_set_client = utf8;

SET [GLOBAL] character_set_connection = utf8;

SET [GLOBAL] character_set_database = utf8;

SET [GLOBAL] character_set_results = utf8;

SET [GLOBAL] character_set_server = utf8;

方法 2、也用 SET 语法，只对本次会话有效

SET NAMES utf8

方法 3、）直接修改 my.cnf，增加一行内容，然后重启 MySQL，使之全局生效

default-character-set = utf8

6.MYSQL 慢查询分析工具：mysqldumpslow

mysqldumpslow 命令

/path/mysqldumpslow -s c -t 10 /database/mysql/slow-log

这会输出记录次数最多的 10 条 SQL 语句，其中：

  * -s, 是表示按照何种方式排序，c、t、l、r 分别是按照记录次数、时间、查询时间、返回的记录数来排序，ac、at、al、ar，表示相应的倒叙；

  * -t, 是 top n 的意思，即为返回前面多少条的数据；

  * -g, 后边可以写一个正则匹配模式，大小写不敏感的；

比如

/path/mysqldumpslow -s r -t 10 /database/mysql/slow-log

得到返回记录集最多的 10 个查询。

/path/mysqldumpslow -s t -t 10 -g“left join”/database/mysql/slow-log

得到按照时间排序的前 10 条里面含有左连接的查询语句。

查看 mysql 的某个选项

show variables like lsquo;%VAR_NAME% rsquo;;

select @@VAR_NAME;

  在下管理 MySQL 的时候总有一些很紧急的情况，发现数据库突然变得压力很大了，那么作为一个 DBA，也许需要一些常用的手段或者说命令去分析问题出现在哪里，然后解决：

数据库突然产生压力时查看正在查询的 SQL:(如果这里内容太多表示并发执行的 SQL 过多，或许数据库堵塞了，会越来越慢，正常情况下这里应该很少有东西的，也就是连接都在 Sleep 状态）

/usr/local//bin/mysql -uroot -ppassword databaseName  -e“show full processlist”| grep -v Sleep

正在运行的 SQL 太多了，看不过来，那需要排序了，看持续执行时间最长的那些 SQL：

/usr/local/mysql/bin/mysql -uroot -ppassword databaseName  -e“show full processlist”| grep -v Sleep | sort -k6rn sort.tmp

如果发现 IOWait 很高，请查看临时表的生成情况，特别是 disk tmp table:

/usr/local/mysql/bin/mysql -uroot -ppassword databaseName  -e“show global status like lsquo;%tmp% rsquo;”

通过这样一些办法可以查看数据库都在忙什么，那些忙的 SQL 又具体在哪一个步骤上卡住了，是在创建磁盘临时文件、Sending Data、statistics？依照不同的原因来解决问题

mdash; mdash; mdash; mdash; mdash; mdash; mdash; mdash; mdash; mdash; mdash; mdash; mdash; mdash; mdash; mdash; mdash; mdash; mdash; mdash; mdash;

关于 Mysql Replication 日常管理，重做，问题分析时常用的办法：

重做 Slave，或者 Master 变化等等，需要将 Slave 与新的 Master 同步：

change master to master_host=IP,master_user= rsquo;replication userName rsquo;,master

_password= rsquo;replication Passwrod rsquo;,master_log_file= rsquo;log-bin.000001 prime;,master_log_pos=0;

导出数据成 SQL 文本，慎用，根据你的 DB 大小会锁表，导致堵塞其他访问：

nohup /usr/local/mysql/bin/mysqldump ndash;database DATABASEName -uUserName -pPassWord ndash;lock-all-tables -F DATA20070519.sql

- F 后会刷新 Master Log 这样配合上面的 Change Master 可以让 Slave 进行同步

只导出数据库的结构（没有任何内容）

/usr/local/mysql/bin/mysqldump -d DATABASEName -uUserName -pPassWord DATA20070519.structure

只导出数据库的数据（没有创建表结构的语句等等）

/usr/local/mysql/bin/mysqldump -t DATABASEName -uUserName -pPassWord DATA20070519.data

同步的时候出现问题（或者其他问题）了，根据同步出现问题的位置（偏移量），查看 Binlog 的具体内容

/usr/local/mysql/bin/mysqlbinlog binlogFileName ndash;start-position= 偏移量

呵呵，我们碰到过 Master 执行的 SQL 到了 Slave 会报语法错误，够诡异吧！不过就是这样查到了原因：如果通过存储过程将 bit 的内容改为 1 就会出现这样的问题，后来将 bit 改为 tinyint(1) 就好了

授权给某一台 Slave 拥有复制的权限：

grant replication slave on *.* to 用户名 @IP identified by lsquo; 密码 rsquo;;

查看 Slave 状态：

Show slave status \G

查看 Master 状态：

Show master status;

重置 Slave（慎用）

reset slave;

Slave 出现问题了，先跳过这一条语句（请确认所要跳过的具体内容不会影响后面的同步，确认方法查看 Binlog 文件）：

set global sql_slave_skip_counter=1; (记得先暂停 Slave：stop slave; 然后重启 Slave：start slave;)

mdash; mdash; mdash; mdash; mdash; mdash; mdash; mdash; mdash; mdash; mdash; mdash; mdash; mdash; mdash; ndash;

纯粹 Linux 相关的：

tcpdump -A“dst port 3306 Prime; 查看 3306 端口的通信具体内容  

上述就是丸趣 TV 小编为大家分享的 MySQL DBA 常用小知识有哪些了，如果刚好有类似的疑惑，不妨参照上述分析进行理解。如果想知道更多相关知识，欢迎关注丸趣 TV 行业资讯频道。