MySQL DBA 常用手册小结
程序员文章站
2023-12-20 18:25:34
1.mysql的远程连接命令可以远程导入导出数据 mysqldump --default-character-set=gb2312 -h255.255.000.00 -ur...
1.mysql的远程连接命令可以远程导入导出数据
mysqldump --default-character-set=gb2312 -h255.255.000.00 -uroot -pxxxxxx mydatabase>d:\data.sql
将指定的数据库导出到一个外部sql文件中去！
还原命令：
mysql --default-character-set=gb2312 -h255.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: 你也可以加上–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 –check-upgrade –all-databases –auto-repair
# mysql_fix_privilege_tables
# mysqlcheck –all-databases –check-upgrade –fix-db-names –fix-table-names
note: 在每一次的升级过程中，mysql_upgrade这个命令我们都应该去执行，它通过mysqlcheck命令帮我们去检查表是否兼容新版本的数据库同时 作出修复，还有个很重要的作用就是使用mysql_fix_privilege_tables命令去升级权限表。
7. 关闭旧版本，将新版的数据库的使用端口改为3306，重新启动新版本mysql数据库。到此，一个简单环境下的数据库升级就结束了。
第二种，同样适用任何存储引擎。
1. 同样先安装好新版本的mysql。
2. 在旧版本中，备份数据库。
# mkdir /opt/mysqlsystems_bk ; mysqldump -p3306 -uroot –tab=/opt/mysqlsystems_bk mysqlsystems_com
note: –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 ‘%var_name%';
select @@var_name;
在linux下管理mysql数据库的时候总有一些很紧急的情况，发现数据库突然变得压力很大了，那么作为一个dba，也许需要一些常用的手段或者说命令去分析问题出现在哪里，然后解决：
数据库突然产生压力时查看正在查询的sql:(如果这里内容太多表示并发执行的sql过多，或许数据库堵塞了，会越来越慢，正常情况下这里应该很少有东西的，也就是连接都在sleep状态）
/usr/local/mysql/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 ‘%tmp%'”
通过这样一些办法可以查看数据库都在忙什么，那些忙的sql又具体在哪一个步骤上卡住了，是在创建磁盘临时文件、sending data、statistics？依照不同的原因来解决问题
—————————————————————
关于mysql replication日常管理，重做，问题分析时常用的办法：
重做slave，或者master变化等等，需要将slave与新的master同步：
change master to master_host=ip,master_user='replication username',master
_password='replication passwrod',master_log_file='log-bin.000001′,master_log_pos=0;
导出数据成sql文本，慎用，根据你的db大小会锁表，导致堵塞其他访问：
nohup /usr/local/mysql/bin/mysqldump –database databasename -uusername -ppassword –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 –start-position=偏移量
呵呵，我们碰到过master执行的sql到了slave会报语法错误，够诡异吧！不过就是这样查到了原因：如果通过存储过程将bit的内容改为1就会出现这样的问题，后来将bit改为tinyint(1)就好了
授权给某一台slave拥有复制的权限：
grant replication slave on *.* to 用户名@ip identified by ‘密码';
查看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;)
———————————————–
纯粹linux相关的：
tcpdump -a “dst port 3306″ 查看3306端口的通信具体内容
mysqldump --default-character-set=gb2312 -h255.255.000.00 -uroot -pxxxxxx mydatabase>d:\data.sql
将指定的数据库导出到一个外部sql文件中去！
还原命令：
mysql --default-character-set=gb2312 -h255.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: 你也可以加上–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 –check-upgrade –all-databases –auto-repair
# mysql_fix_privilege_tables
# mysqlcheck –all-databases –check-upgrade –fix-db-names –fix-table-names
note: 在每一次的升级过程中，mysql_upgrade这个命令我们都应该去执行，它通过mysqlcheck命令帮我们去检查表是否兼容新版本的数据库同时 作出修复，还有个很重要的作用就是使用mysql_fix_privilege_tables命令去升级权限表。
7. 关闭旧版本，将新版的数据库的使用端口改为3306，重新启动新版本mysql数据库。到此，一个简单环境下的数据库升级就结束了。
第二种，同样适用任何存储引擎。
1. 同样先安装好新版本的mysql。
2. 在旧版本中，备份数据库。
# mkdir /opt/mysqlsystems_bk ; mysqldump -p3306 -uroot –tab=/opt/mysqlsystems_bk mysqlsystems_com
note: –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 ‘%var_name%';
select @@var_name;
在linux下管理mysql数据库的时候总有一些很紧急的情况，发现数据库突然变得压力很大了，那么作为一个dba，也许需要一些常用的手段或者说命令去分析问题出现在哪里，然后解决：
数据库突然产生压力时查看正在查询的sql:(如果这里内容太多表示并发执行的sql过多，或许数据库堵塞了，会越来越慢，正常情况下这里应该很少有东西的，也就是连接都在sleep状态）
/usr/local/mysql/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 ‘%tmp%'”
通过这样一些办法可以查看数据库都在忙什么，那些忙的sql又具体在哪一个步骤上卡住了，是在创建磁盘临时文件、sending data、statistics？依照不同的原因来解决问题
—————————————————————
关于mysql replication日常管理，重做，问题分析时常用的办法：
重做slave，或者master变化等等，需要将slave与新的master同步：
change master to master_host=ip,master_user='replication username',master
_password='replication passwrod',master_log_file='log-bin.000001′,master_log_pos=0;
导出数据成sql文本，慎用，根据你的db大小会锁表，导致堵塞其他访问：
nohup /usr/local/mysql/bin/mysqldump –database databasename -uusername -ppassword –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 –start-position=偏移量
呵呵，我们碰到过master执行的sql到了slave会报语法错误，够诡异吧！不过就是这样查到了原因：如果通过存储过程将bit的内容改为1就会出现这样的问题，后来将bit改为tinyint(1)就好了
授权给某一台slave拥有复制的权限：
grant replication slave on *.* to 用户名@ip identified by ‘密码';
查看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;)
———————————————–
纯粹linux相关的：
tcpdump -a “dst port 3306″ 查看3306端口的通信具体内容