mysql维护记录

最近公司的流量巨增，MYSQL被压跨了，没有DBA，恶补了一下MYSQL的一些知识，把过程中遇到的一些问题有解决方案记录下了，

有些是从网上找到的，有些是自己总结的，暂且设为原创吧，因为好多资料网上一搜都一大把，倒底出自哪，都不知道了。

mysql 主从配置，

假设有两台机器：db1和db2.我们可以设db1设为主服务器，所有写的操作在主服务器上操作，db2为从服务器，所有读的操作，可以在db2上操作。
两台机器都布置好，装上mysqld并做好相关优化工作。确保MYSQL的版本要一至
然后在db1上进行如下操作：
  #vi /etc/my.cnf
       [mysqld]
       log-bin=mysql-bin   //[必须]启用二进制日志
       server-id=1      //[必须]服务器唯一ID，默认是1，所有参与主从同步的机器，server-id不能相同。
同样在db2上进行如下操作：
 #vi /etc/my.cnf
       [mysqld]
       log-bin=mysql-bin   //[必须]启用二进制日志
       server-id=2     //[必须]服务器唯一ID，默认是1，所有参与主从同步的机器，server-id不能相同。
在主服务器上创建同步用户，也可以使用root但一般不建议
#/usr/local/mysql/bin/mysql -uroot -pmttang  
   mysql>GRANT REPLICATION SLAVE ON *.* to ‘syncuser’@‘%’ identified by '123456'; //
用户名和密码自己根据情况设定。

查看db1的master情况：
mysql>show master status;
   +------------------+----------+--------------+------------------+
   | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
   +------------------+----------+--------------+------------------+
   | mysql-bin.000004 |      308 |              |                  |
   +------------------+----------+--------------+------------------+
   1 row in set (0.00 sec)
记录下上面的file和position值

这时不要对主数据库做任何写的操作，

把数据库导出到db2.导的方法无所谓。
导完后设置从库的同步属性：
mysql>change master to master_host='db1’,master_user=‘syncuser’,master_password='123456',
         master_log_file='mysql-bin.000004',master_log_pos=308;
db1为主服务器的地址，用户名和密码为上面创建的，log_file和log_pos为前面show master status所记下的。
执行完成后，启动同步操作：
  Mysql>start slave;
在从库内执行：
mysql> show slave status\G
可以查看同步情况，必须确保
    Slave_IO_Running: Yes    //此状态必须YES
    Slave_SQL_Running: Yes     //此状态必须YES
如果有一个为no则查一下error，看是什么错误，

然后操作完后，在主库上添加一个记录或是添加一张表，看下从库同否也同步了。

增加一台从库
运行一段时间 后，如果需要增加一台从库db3，可以按如下步骤进行：
将db3安装上相同版本的mysql,并设置好server-id为3或其他任何值，只要不同已有的同库或主库重复即可。
注意，如果mysql的目录是从db2拷贝过来的，记得删除掉data目录的auto.cnf以确保server-uuid也不相同。
#vi /etc/my.cnf
       [mysqld]
       log-bin=mysql-bin   //[必须]启用二进制日志
       server-id=3      //[必须]服务器唯一ID，默认是1，所有参与主从同步的机器，server-id不能相同。
先在db2上暂定同步进程：
mysql>stop slave;
记录下同步状态：
mysql> show slave status\G
主要是以下两个值 ：
  Relay_Master_Log_File: mysql-bin.000004
  Exec_Master_Log_Pos: 308
然后将从库db2导出到新的从库db3
导出后，可以先启动db2的同步功能：
mysql>start slave;
然后在db3上做如下操作：
mysql>change master to master_host='db1’,master_user=‘syncuser’,master_password='123456',
         master_log_file='mysql-bin.000004',master_log_pos=308;
其中：
master_log_file='mysql-bin.000004',master_log_pos=308; 就是在db2记录的
 Relay_Master_Log_File: mysql-bin.000004和
  Exec_Master_Log_Pos: 308
设置好了后，就可以在db3上启动同步：
mysql>start slave;

这么做的好处是，增加slave不同锁定主库，因为在业务量大时，一般主库是不能锁的。但在导出的过程中，从库db2是未同步状态，与主库的数据是有差别的，注意下业务上有没有影响，如果实在不行，就把所有读操作都切换到主库上，再做如上操作。

如果在已有未做主从的情况下操作增加从库的功能，可以有如下两种（主机不能停止的情况下）：
1.mysqldump方法：
备份主库
# mysqldump -uroot -p123 --routines --single_transaction --master-data=2 --all-databases  > all_db.sql
--routines：导出存储过程和函数
--single_transaction：导出开始时设置事务隔离状态，并使用一致性快照开始事务，然后unlock tables;而lock-tables是锁住一张表不能写操作，直到dump完毕。
--master-data：默认等于1，将dump起始(change master to)binlog点和pos值写到结果中，等于2是将change master to写到结果中并注释。
把备份库拷贝到从库
并查看bingo点和pos:
# head -25 all_db.sql
如：
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=107;   #大概22行
从库设置从这个日志点同步，并启动同步功能：
mysql> change master to master_host=‘db1’,
    -> master_user='syncuser’,
    -> master_password=‘123456’,
    -> master_log_file='mysql-bin.000001',
    -> master_log_pos=107;
mysql> start slave;
这种处理方法，在导出的时候，会锁表的，导出后，又解锁了。如果完全不锁表，可以用第二种 方法：
2.xtrabackup方式(推荐)需要安装第三方工具。
  # innobackupex --user=root --password=123 ./
 生成一个以时间为命名的备份目录：2015-07-01_16-49-43
 把备份目录拷贝到从库上
从库上把MySQL服务停掉，删除datadir目录，将备份目录重命名为datadir目录
从备份目录中xtrabackup_info文件获取到binlog和pos位置
# cat /var/lib/mysql/xtrabackup_info 
 uuid = 201af9db-1fce-11e5-96b0-525400e4239d
 name = 
 tool_name = innobackupex
 tool_command = --user=root --password=... ./
 tool_version = 1.5.1-xtrabackup
 ibbackup_version = xtrabackup version 2.2.11 based on MySQL server 5.6.24 Linux (x86_64) (revision id: )
 server_version = 5.5.43-0ubuntu0.12.04.1-log
 start_time = 2015-07-01 16:49:43
 end_time = 2015-07-01 16:49:46
 lock_time = 1
 binlog_pos = filename 'mysql-bin.000001', position 429    #这个位置
 innodb_from_lsn = 0
 innodb_to_lsn = 1598188
 partial = N
 incremental = N
 format = file
 compact = N
 compressed = N


从库设置从这个日志点同步，并启动
mysql> change master to master_host=‘db1’,
    -> master_user='syncuser’,
    -> master_password=‘123456’,
    -> master_log_file='mysql-bin.000001',
    -> master_log_pos=429;
mysql> start slave;
完成添加从库的动作。
个人总结：其实主要就是要找出主机的binlog点和pos位置，并导出对应的数据库即可，然后从库导入这个数据后，就可以从这个点从主库请求日志进行同步操作。因为主库是时刻有新增的，就变得有点麻烦。


mysql 日志操作

mysql有以下几种日志：


错误日志： -log-err


查询日志： -log


慢查询日志: -log-slow-queries


更新日志: -log-update


二进制日志： -log-bin


默 认情况下，所有日志创建于mysqld数据目录中。通过刷新日志，你可以强制 mysqld来关闭和重新打开日志文件（或者在某些情况下切换到一个新的日志）。当你执行一个FLUSH LOGS语句或执行mysqladmin flush-logs或mysqladmin refresh时，出现日志刷新
1. 错误日志
用--log- error[=file_name]选项来指定mysqld保存错误日志文件的位置。如果没有给定file_name值，mysqld使用错误日志名 host_name.err 并在数据目录中写入日志文件。如果你执行FLUSH LOGS，错误日志用-old重新命名后缀并且mysqld创建一个新的空日志文件。(如果未给出--log-error选项，则不会重新命名）。

如果不指定--log-error，或者(在Windows中)如果你使用--console选项，错误被写入标准错误输出stderr。通常标准输出为你的终端。
my.cnf内配置为：
[mysqld_safe]
log-error=/var/log/mysqld.log

通用查询日志                                                           
用--log[=file_name]或-l [file_name]选项启动它。如果没有给定file_name的值，默认名是host_name.log。
慢速查询日志                                                           
用--log-slow-queries[=file_name]选项启动时，mysqld 写一个包含所有执行时间超过long_query_time秒的SQL语句的日志文件.如果没有给出file_name值，默认未主机名，后缀为 -slow.log。如果给出了文件名，但不是绝对路径名，文件则写入数据目录。
更新日志                                                             
用--log-update[=file_name]选项启动,不推荐使用.

是否启用了日志
mysql>show variables like 'log_%';
怎样知道当前的日志
mysql> show master status;
顯示二進制日志數目
mysql> show master logs;
看二进制日志文件用mysqlbinlog
shell>mysqlbinlog mail-bin.000001
或者shell>mysqlbinlog mail-bin.000001 | tail
在配置文件中指定log的輸出位置.
Windows：Windows 的配置文件为 my.ini，一般在 MySQL 的安装目录下或者 c:\Windows 下。
Linux：Linux 的配置文件为 my.cnf ，一般在 /etc 下。
在linux下：
Sql代码
# 在[mysqld] 中輸入
#log
log-error=/usr/local/mysql/log/error.log
log=/usr/local/mysql/log/mysql.log
long_query_time=2
log-slow-queries= /usr/local/mysql/log/slowquery.log
# 在[mysqld] 中輸入 #log
log-error=/usr/local/mysql/log/error.log
log=/usr/local/mysql/log/mysql.log
long_query_time=2
log-slow-queries= /usr/local/mysql/log/slowquery.log

windows下:
Sql代码
# 在[mysqld] 中輸入
#log
log-error="E:/PROGRA~1/EASYPH~1.0B1/mysql/logs/error.log"
log="E:/PROGRA~1/EASYPH~1.0B1/mysql/logs/mysql.log"
long_query_time=2
log-slow-queries= "E:/PROGRA~1/EASYPH~1.0B1/mysql/logs/slowquery.log"
# 在[mysqld] 中輸入 #log
log-error="E:/PROGRA~1/EASYPH~1.0B1/mysql/logs/error.log"
log="E:/PROGRA~1/EASYPH~1.0B1/mysql/logs/mysql.log"
long_query_time=2
log-slow-queries= "E:/PROGRA~1/EASYPH~1.0B1/mysql/logs/slowquery.log"

开启慢查询
long_query_time =2 --是指执行超过多久的sql会被log下来，这里是2秒
网上有很多文章都会让设置这个参数：
log-slow-queries＝path-to-log
但本人设置了，启动时，会报log-slow-queries不是合法的命令，不知道为什么。
我是使用以下参数指定慢查询日志文件的：
slow-query-log-file = /var/log/mysqld_slow.log--将查询返回较慢的语句进行记录
log-queries-not-using-indexes = nouseindex.log --就是字面意思，log下来没有使用索引的query
log=mylog.log --对所有执行语句进行记录
windows下开启mysql日志：
在[mysql]下加入这些（基本上等于加在最后面）：
log-error=
#Enter a name for the query log file. Otherwise a default name will be used.
#注:（写成txt文件editplus可以及时重载,不过有时要放在C盘下editplus才可以及时重载）
log= c:/mysql_query.log.txt
#Enter a name for the slow query log file. Otherwise a default name will be used.
slow-query-log-file=
Enter a name for the update log file. Otherwise a default name will be used.
log-update=
#Enter a name for the binary log. Otherwise a default name will be used.
log-bin=

＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝MYSQL常见错误处理＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝

一、mysql 出现以下错误：
Table "mysql"."innodb_table_stats" not found.
InnoDB: Error: Fetch of persistent statistics requested for table "guser_db"."guser_temporary_user" but the required system tables mysql.innodb_table_stats and mysql.innodb_index_stats are not present or have unexpected structure. Using transient stats instead.

说明MYSQL库内的innodb表有问题，一般这种情况是因为数据库初始化的时候,dba可能删除过ibdata1文件
虽然重启之后,数据库会自动创建一个ibdata1文件,但是上述系统表也是innodb引擎,所以不能访问了.
这虽然不会影响业务,但是使用innobackupex备份的时候,会写入错误日志.
最后错误日志里,都是这种信息.影响日常检查。

解决思路，在数据库内，删除以下几张表：
drop table mysql.innodb_index_stats;
drop table mysql.innodb_table_stats;
drop table mysql.slave_master_info;
drop table mysql.slave_relay_log_info;
drop table mysql.slave_worker_info;
如果提示表不存在，再show tables一下看看，可能就没了，确保以上五张表都看不到后，再删除这些表的物理文件：

rm -rf innodb_index_stats*
rm -rf innodb_table_stats*
rm -rf slave_master_info*
rm -rf slave_relay_log_info*
rm -rf slave_worker_info*
再用语句创建以上五张表，创建完成后，重启数据库，
注意，如果是主从数据库的从库进行设置的话，最好在上面drop table的之前，先停止从库：
stop slave;
然后记录一下同步状态：
show slave status; 记录下：Relay_Master_Log_File和Exec_Master_Log_Pos的值，以便在重启后，有同步失败的情况时，再重新change master
再进行以上操作，操作完后重启数据库后，再看下同步状态：
show slave status;
同步没问题，就可以了。

二、参与主从同步的mysql 出错以下错误：
2014-05-29 14:35:35 16770 [Note] Slave: received end packet from server, apparent master shutdown:
2014-05-29 14:35:35 16770 [Note] Slave I/O thread: Failed reading log event, reconnecting to retry, log \'mysql-bin.000005\' at position 407
2014-05-29 14:35:35 16770 [Warning] Storing MySQL user name or password information in the master info repository is not secure and is therefore not recommended.
两种问题：
1，server-id相同
2，server-uuid相同
解决方案：
先在数据库里：show variables like ‘%server%’;
看一下显示的server_id和server_uuid，在几台从服务器上都看一下，看下倒底是哪个相同，如果是server-id相同，则在从服务器的my.cnf内设置为不同的即可，如果是server-uuid相同，则删除掉一台服务器上的data目录下的auto.cnf文件，重启从服务器，服务器就会自动创建新的UUID，即可。
出现此情况，一般都是增加从库时，直接将原从库的data文件直接拷贝过来所引起的。

三、数据库出现performance_schema内表结构出错：
140213 16:55:47 [ERROR] Native table 'performance_schema'.'events_waits_current' has the wrong structure
140213 16:55:47 [ERROR] Native table 'performance_schema'.'events_waits_history' has the wrong structure
140213 16:55:47 [ERROR] Native table 'performance_schema'.'events_waits_history_long' has the wrong structure
140213 16:55:47 [ERROR] Native table 'performance_schema'.'setup_consumers' has the wrong structure
140213 16:55:47 [ERROR] Native table 'performance_schema'.'setup_instruments' has the wrong structure
140213 16:55:47 [ERROR] Native table 'performance_schema'.'setup_timers' has the wrong structure
140213 16:55:47 [ERROR] Native table 'performance_schema'.'performance_timers' has the wrong structure
140213 16:55:47 [ERROR] Native table 'performance_schema'.'threads' has the wrong structure
140213 16:55:47 [ERROR] Native table 'performance_schema'.'events_waits_summary_by_thread_by_event_name' has the wrong structure
140213 16:55:47 [ERROR] Native table 'performance_schema'.'events_waits_summary_by_instance' has the wrong structure

执行以下命令重置一下：
/usr/local/mysql/bin/mysql_upgrade -u root -p
即可修复。