GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY '123456' WITH GRANT OPTION;

1.配置Master主服务器
centos2.0
（1）在Master MySQL上创建一个用户‘repl’，并允许其他Slave服务器可以通过远程访问Master，通过该用户读取二进制日志，实现数据同步。

1 mysql>create user repl; //创建新用户
2 //repl用户必须具有REPLICATION SLAVE权限，除此之外没有必要添加不必要的权限，密码为mysql。说明一下192.168.0.%，
这个配置是指明repl用户所在服务器，这里%是通配符，表示192.168.0.0-192.168.0.255的Server都可以以repl用户登陆主服务器。
当然你也可以指定固定Ip。
3 mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'192.168.0.%' IDENTIFIED BY '123456';

（2）找到MySQL安装文件夹修改my.Ini文件。mysql中有好几种日志方式，这不是今天的重点。我们只要启动二进制日志log-bin就ok。
cd /usr/local/mysql

vim my.cnf

添加
server-id=1      //给数据库服务的唯一标识，一般为大家设置服务器Ip的末尾号
log-bin=master-bin
log-bin-index=master-bin.indx

重起数据库服务器
service mysqld restart

（3）查看日志
show master status;

3、配置Slave从服务器(centos3.0)

cd /usr/local/mysql

vim my.cnf

添加
server-id=2
relay-log-index=slave-relay-bin.index
relay-log=slave-relay-bin

重起数据库服务器
service mysqld restart

（2）连接Master
mysql -uroot -p

change master to master_host='192.168.0.105', //Master 服务器Ip
master_port=3306,
master_user='repl',
master_password='123456',
master_log_file='master-bin.000001',//Master服务器产生的日志
master_log_pos=0;

（3）启动Slave

start slave;

测试主从

SHOW SLAVE STATUS

Fatal error: The slave I/O thread stops because master and slave have equal MySQL server UUIDs; these UUIDs must be different for replication to work
master and slave have equal MySQL server UUIDs 解决方法

SHOW VARIABLES LIKE 'server_id'

主从并不一样，排除该问题。

继续排查，找到原因在于，拷贝整个data目录，把auto.cnf文件也拷贝过来了，里面记录了数据库的uuid，每个库的uuid应该是不一样的。

[auto]
server-uuid=6dcee5be-8cdb-11e2-9408-90e2ba2e2ea6

解决办法，按照这个16进制格式，随便改下，重启mysql即可

3、解决故障
###查看auto.cnf文件
[root@dbsrv1 ~] cat /data/mysqldata/auto.cnf  ### 主上的uuid
[auto]
server-uuid=62ee10aa-b1f7-11e4-90ae-080027615026

[root@dbsrv2 ~]# more /data/mysqldata/auto.cnf ###从上的uuid，果然出现了重复，原因是克隆了虚拟机，只改server_id不行
[auto]
server-uuid=62ee10aa-b1f7-11e4-90ae-080027615026

[root@dbsrv2 ~]# mv /data/mysqldata/auto.cnf  /data/mysqldata/auto.cnf.bk  ###重命名该文件
[root@dbsrv2 ~]# service mysql restart          ###重启mysql
Shutting down MySQL.[  OK  ]
Starting MySQL.[  OK  ]
[root@dbsrv2 ~]# more /data/mysqldata/auto.cnf  ###重启后自动生成新的auto.cnf文件，即新的UUID
[auto]
server-uuid=6ac0fdae-b5d7-11e4-a9f3-0800278ce5c9


###再次查看slave的状态已经正常
[root@dbsrv1 ~]# mysql -uroot -pxxx -e "show slave status\G"|grep Running
Warning: Using a password on the command line interface can be insecure.
            Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
      Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it

###主库端查看自身的uuid
master_mysql> show variables like 'server_uuid';
+---------------+--------------------------------------+
| Variable_name | Value                                |
+---------------+--------------------------------------+
| server_uuid  | 62ee10aa-b1f7-11e4-90ae-080027615026 |
+---------------+--------------------------------------+
1 row in set (0.00 sec)

###主库端查看从库的uuid
master_mysql> show slave hosts;
+-----------+------+------+-----------+--------------------------------------+
| Server_id | Host | Port | Master_id | Slave_UUID                          |
+-----------+------+------+-----------+--------------------------------------+
|        33 |      | 3306 |        11 | 62ee10aa-b1f7-11e4-90ae-080027615030 |
|        22 |      | 3306 |        11 | 6ac0fdae-b5d7-11e4-a9f3-0800278ce5c9 |
+-----------+------+------+-----------+--------------------------------------+
### Author : Leshami
### Blog  : http://www.linuxidc.com

4、延生参考
a、有关server_id的描述
The server ID, used in replication to give each master and slave a unique identity. This variable is set
by the --server-id option. For each server participating in replication, you should pick a
positive integer in the range from 1 to 232– 1(2的32次方减1) to act as that server's ID.

 

b、有关 server_uuid的描述
Beginning with MySQL 5.6, the server generates a true UUID in addition to the --server-id
supplied by the user. This is available as the global, read-only variable server_uuid(全局只读变量)

When starting, the MySQL server automatically obtains a UUID as follows:
a).  Attempt to read and use the UUID written in the file data_dir/auto.cnf (where data_dir is
the server's data directory); exit on success.
b). Otherwise, generate a new UUID and save it to this file, creating the file if necessary.
The auto.cnf file has a format similar to that used for my.cnf or my.ini files. In MySQL 5.6,
auto.cnf has only a single [auto] section containing a single server_uuid [1992] setting and
value;  

Important
The auto.cnf file is automatically generated; you should not attempt to write
or modify this file

Also beginning with MySQL 5.6, when using MySQL replication, masters and slaves know one
another's UUIDs. The value of a slave's UUID can be seen in the output of SHOW SLAVE HOSTS. Once
START SLAVE has been executed (but not before), the value of the master's UUID is available on the
slave in the output of SHOW SLAVE STATUS.

In MySQL 5.6.5 and later, a server's server_uuid is also used in GTIDs for transactions originating
on that server. For more information, see Section 16.1.3, “Replication with Global Transaction

本文永久更新链接地址：http://www.linuxidc.com/Linux/2015-02/113564.htm
linux