mysql主从服务器配置

前言：假设主服务器为AS(ip:192.168.20.9),从服务器为BS（ip192.168.20.8），操作如下：

• 假如BS上还没有数据库，需要先把要同步的数据库拷贝一份到BS上。

  注意：对AS的数据库进行备份时，需要先停掉AS上的mysql服务，备份好之后再重启

• 在AS上进行主服务器的配置，编辑AS上的/etc/my.cnf文件（编辑之前请先备份），在文件中加入如下内容：

  log-bin=mysql-bin
  server-id=1
  binlog-do-db=guser 
  binlog-do-db=gm
  binlog-do-db=gm_old
  ......
  
  binlog-ignore-db=information_schema,horde,mysql,phpmyadmin_5l8JCTUK8xa9,phpmyadmin_l2RCxzQZbD5x,psa

  其中binlog-do-db配置的是要同步的数据库名，binlog-ignore-db配置的是不同步的数据库名

• 重启AS上的mysql使修改生效：

  service mysqld restart

• 在AS上建立一个有操作数据库权限的账户，如：luna;gotobus888，然后用改账号登录mysql

  mysql -u luna -p

  在提示中输入密码进入mysql界面，在mysql界面再输入如下指令：

  mysq> show master status;

  然后记录下下图所示数据，file=mysql-bin.000001，pos=4500

  
• 在BS上进行从服务器的配置，编辑BS上的/etc/my.cnf文件（编辑前先备份），在文件中加入如下内容：

  log-bin=mysql-bin
  server-id=3
  relay_log=mysql-relay-bin
  log_slave_updates=1
  
  replicate-do-db=guser
  replicate-do-db=gm
  replicate-do-db=gm_old
  ......
  
  slave-skip-errors=all
  

  replicate-do-db配置要同步的数据库名，同主服务器上一致

• 重启BS上的mysql使修改生效：

  service mysqld restart

• 在从服务器BS上输入如下指令进入mysql界面：

  mysql -uadmin -p`cat /etc/psa/.psa.shadow`; 

  在mysql界面再输入如下指令修改slave的参数：

  slave stop;
  change master to
  master_host='192.168.0.1',
  master_user='backup'，
  master_password='123456'，
  master_log_file='mysql-bin.000001',  #此处填写主服务器的日志文件名
  master_log_pos=4500; #此处填写主服务器日志文件记录的位置
  slave start;
  

  输入如下指令检查slave状态：

  show slave status \G;

  结果如下图所示，则表示配置成功（注意红线框内的内容）

  

常见问题总结

1. 主从同步报错，show slave status\G:显示结果如下：

   Slave_SQL_Running: NO
   Seconds_Behind_Master: NULL
   

   解决办法，在mysql界面输入如下指令

   stop slave;
   set global sql_slave_skip_counter =1 ;
   start slave;
   

   此时show slave status\G:显示结果如下：

   Slave_IO_Running: Yes
   Slave_SQL_Running: Yes
   Seconds_Behind_Master:0

2. 在主服务器上修改过my.cnf中的数据，并重启过数据库后，在从服务器启动slave会报错，此时可用如下指令：

   stop slave;
   reset slave;
   start slave;

3. mysql执行过程中提示“table ’ ***’is read only”，首先可以用如下指令查看当前用户的权限

   mysql> SHOW GRANTS FOR CURRENT_USER;
   +-----------------------------------------------------------------------------+
   | Grants for dbuser@%                                                          |
   +-----------------------------------------------------------------------------+
   | GRANT USAGE ON *.* TO 'dbuser'@'%' IDENTIFIED BY PASSWORD 'randomhex' |
   | GRANT ALL PRIVILEGES ON `dbname`.* TO 'dbuser'@'%'                         |
   +-----------------------------------------------------------------------------+
   2 rows in set (0.00 sec)
   

   mysql> REPAIR TABLE tbl_name;
   +-------------------+--------+----------+----------------------------------------+
   | Table             | Op     | Msg_type | Msg_text                               |
   +-------------------+--------+----------+----------------------------------------+
   | dbuser.tbl_name| repair | error    | Table 'dbname.tbl_name' is read only |
   +-------------------+--------+----------+----------------------------------------+
   1 row in set, 1 warning (0.00 sec)

   假如问题还没有得到解决，查看/var/lib/mysql/目录下的文件是否都属于mysql:mysql（包括数据库子目录），修改权限后要重启mysqld。并且重新配置master的参数

4. 主服务器数据库出现了一些问题，要将从服务器转为主服务器。如何干净的清楚slave信息

   mysql > slave stop;
   mysql > reset slave all;

   重启mysql服务,用show slave status \G;查看状态，结果如下：

   mysql> show slave status \G;
   Empty set (0.00 sec)
   
   ERROR: 
   No query specified
   

   假如重新生成了master.info,relay-log.info，试一下CHANGE MASTER TO MASTER_HOST=”;

5. 用mysqldump备份数据库时，有时会报如下错

   mysqldump: Got error: 145: Table './wordpress***/wp_user***' is marked as crashed and should be repaired when using LOCK TABLES

   这是因为数据表当时本身有问题，用以下方式解决

   check tables wp_user***
   repair tables wp_user***