mysql简单复制服务搭建

1.安装mysql源（centos7中默认是不包含mysql源）
wget http://repo.mysql.com/mysql-community-release-el7-5.noarch.rpm
rpm -ivh mysql-community-release-el7-5.noarch.rpm 
yum install mysql-server

2.配置mysql-master
在my.cnf中添加：
prot=3306
log-bin=master-bin
log-bin-index=master-bin.index
server-id=1

3.重启mysql后新增复制用户
create user repl_user;
grant replication slave on *.* to repl_user identified by 'xyzzy';

4.配置备机mysql
在my.cnf中添加下面几行
relay-log=slave-bin
relay-log-index=slave-bin.index
server-id=2

5.发现备机无法连接到master服务器
nmap -v materIP  发现没有打开ssh的22端口
故打开：
firewall-cmd --zone=public --add-port=22/tcp --permanent

6.slave数据库报错
有报错信息：

mysql> CHANGE MASTER TO MASTER_HOST='192.168.52.129',

-> MASTER_USER='repl',

-> MASTER_PASSWORD='repl_1234',

-> MASTER_LOG_FILE='mysql-bin.000141',

-> MASTER_LOG_POS=120;

ERROR 1794 (HY000): Slave is not configuredor failed to initialize properl
y. You must at least set --server-id to enableeither a master or a slave. 
Additional error messages can be found in the MySQLerror log.

mysql>SHOW VARIABLES LIKE 'server_id';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id     | 0     |
+---------------+-------+
1 row in set (0.00 sec)

id没有生效
配置里面有[mysqld]和[mysqld_safe]，新增的配置文件放的位置不一样,将新增配置
放到mysqld下试试，ok成功。


mysql> show variables like 'server_id';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id     | 2     |
+---------------+-------+
1 row in set (0.00 sec)

mysql> change master to 
    -> master_host='192.168.122.1'
    -> ,master_port=3306,
    -> master_user='repl_user',
    -> master_password='xyzzy';
Query OK, 0 rows affected, 2 warnings (0.05 sec)

mysql> start slave;
Query OK, 0 rows affected (0.01 sec)
测试复制是否成功：
在master上创建一个数据库：
mysql> create database cydb;
Query OK, 1 row affected (0.00 sec)
在slave上查看是否有此数据库：
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| cydb               |
| mysql              |
| performance_schema |
+--------------------+
4 rows in set (0.00 sec)
至此，简单的复制环境搭配成功。

 错误：在用mysqldump 备份master以及在slave导入时均报权限不足，信息如下：

[root@localhost ~]# mysqldump --all-databases  >/tmp/backup.sqlmysqldump: Got error: 1045: Access denied for user 'root'@'localhost' (using password: NO) when trying to connect


[root@localhost mysql]# mysql --host=192.168.122.59 </tmp/backup.sql 
ERROR 1045 (28000): Access denied for user 'root'@'192.168.122.59' (using password: NO)

原因：root用户的host不是localhost，用如下语句查询：

select * from mysql.user where host!='127.0.0.1' and host!='localhost';

 因此将root用户的host改为localhost（本地连接） 或者"%"（表示所有ip均可连接），重置密码后可以正确备份和导入了。