在slave上使用mysql登陆
(1)Mysql> grant all privileges on backup.* to ‘backup’@’192.168.37.188’ identified by ‘1234’;
(2)Mysql>flush privileges;
刷新权限设置;
说明:上面的all privileges在4.0版上应该为replication slave,也就是 grant replication slave on ........在3.23上是file,也就是grant file on ........ 但是我怕有别的麻烦,干脆权限全给好啦.
在进行如上设置之后,可以看出在192.168.37.189设定好并重启mysql以后,mysql会在数据目录 (/var/lib/mysql)下生成一个master.info文件和relay-log.info,relay-log.index文件.如果要更改master服务器,则要删除掉这个文件,(即在更改了/etc/my.cnf中master相关信息)在my.cnf文件中重新配置,重新启动 mysql,更改才会生效.
方法二:show processlist;
如果正确,则应该如下所示:
Mysql>show processlist;
+----+-------------+-----------+------+---------+------+-----------------------------------------------------------------------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+-------------+-----------+------+---------+------+-----------------------------------------------------------------------+------------------+
| 4 | system user | | NULL | Connect | 398 | Waiting for master to send event | NULL |
| 5 | system user | | NULL | Connect | 398 | Has read all relay log; waiting for the I/O slave thread to update it | NULL |
| 6 | root | localhost | NULL | Query | 0 | NULL | show processlist |
+----+-------------+-----------+------+---------+------+-----------------------------------------------------------------------+------------------+
3 rows in set (0.00 sec)
注意同标记过的字符类似,则是正确的,错误情况下应该是这个样子:
mysql> show processlist;
+----+-------------+-----------+------+---------+------+-----------------------------------------------------------------------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+-------------+-----------+------+---------+------+-----------------------------------------------------------------------+------------------+
| 4 | system user | | NULL | Connect | 454 | Reconnecting after a failed master event read | NULL |
| 5 | system user | | NULL | Connect | 454 | Has read all relay log; waiting for the I/O slave thread to update it | NULL |
| 7 | root | localhost | NULL | Query | 0 | NULL | show processlist |
+----+-------------+-----------+------+---------+------+-----------------------------------------------------------------------+------------------+
3 rows in set (0.00 sec)
当然如果这里的Reconnecting只是错误的一种,有可能是connecting,则表示正在连接,那么请检查:
1 master上的mysql daemon是否正常运行
2 master与slave的网络连接是否正常
3 my.cnf是否配置正确
4 在修改配置后是否删除过master.info?(删掉以后会自动再生成一个,别担心删掉),因为如果不删掉的话,那么则还是使用原来的配置
5 修改配置后有没有重新启动mysql daemon,重新启动过程后必须证实mysql已经正常启动
6 master上给slave及slave给master上分配的replication用户权限是否正确,master的主机名和dns设置
7 当前状况两台数据库是否完全相同.
方法三:show master status;
mysql> show master status;
+----------------+----------+--------------+------------------+
| File | Position | Binlog_do_db | Binlog_ignore_db |
+----------------+----------+--------------+------------------+
| Server-bin.021 | 79 | backup | |
+----------------+----------+--------------+------------------+
1 row in set (0.00 sec)
注意上边的这条,position不能为0,如果为0则表示有问题,请检查/etc/my.cnf中的server-id及是否打开log-bin
mysql> show processlist;
+----+--------+---------------------+------+-------------+------+----------------------------------------------------------------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+--------+---------------------+------+-------------+------+----------------------------------------------------------------+------------------+
| 1 | backup | 192.168.37.189:1067 | NULL | Binlog Dump | 284 | Has sent all binlog to slave; waiting for binlog to be updated | NULL |
| 3 | root | localhost | NULL | Query | 0 | NULL | show processlist |
+----+--------+---------------------+------+-------------+------+----------------------------------------------------------------+------------------+
2 rows in set (0.00 sec)
如果master上不是这样,那么就应该是master的配置有问题啦.
方法四 查看错误日志
在/var/lib/mysql下有个hostname.err文件,所有的错误都在其中被记录,如下所示:
041210 1251 mysqld started
041210 1251 Warning: Asked for 196608 thread stack, but got 126976
InnoDB: The first specified data file ./ibdata1 did not exist:
InnoDB: a new database to be created!
041210 1251 InnoDB: Setting file ./ibdata1 size to 10 MB
InnoDB: Database physically writes the file full: wait...
041210 12:54:54 InnoDB: Log file ./ib_logfile0 did not exist: new to be created
InnoDB: Setting log file ./ib_logfile0 size to 5 MB
InnoDB: Database physically writes the file full: wait...
041210 12:54:55 InnoDB: Log file ./ib_logfile1 did not exist: new to be created
InnoDB: Setting log file ./ib_logfile1 size to 5 MB
InnoDB: Database physically writes the file full: wait...
InnoDB: Doublewrite buffer not found: creating new
InnoDB: Doublewrite buffer created
InnoDB: Creating foreign key constraint system tables
InnoDB: Foreign key constraint system tables created
041210 12:54:58 InnoDB: Started
/usr/sbin/mysqld: ready for connections.
Version: '4.0.20-standard-log' socket: '/var/lib/mysql/mysql.sock' port: 3306
041210 12:54:58 Slave SQL thread initialized, starting replication in log 'FIRST' at position 0, relay log './Jintao-relay-bin.001' position: 4
041210 12:54:58 Slave I/O thread: connected to master '[email protected]:3306', replication started in log 'FIRST' at position 4
以上日志没有错误