【转载】MySQL Replication 环境安装与配置

安装
[root@msr01 ~]# yum install mysql-server
Installed:
mysql-server.x86_64 0:5.1.73-3.el6_5

Dependency Installed:
mysql.x86_64 0:5.1.73-3.el6_5
perl-DBD-MySQL.x86_64 0:4.013-3.el6
perl-DBI.x86_64 0:1.609-4.el6

启动并修改密码
[root@msr01 ~]# service mysqld start

Please report any problems with the /usr/bin/mysqlbug script!

[ OK ]
Starting mysqld: [ OK ]
[root@msr01 ~]# /usr/bin/mysqladmin -u root password ‘mysqlpass’
[root@msr01 ~]#
查看默认的my.cnf配置
[root@msr01 ~]# cat /etc/my.cnf
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
[root@msr01 ~]#

修改主服务器my.cnf配置
[root@msr01 ~]# vi /etc/my.cnf
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

server-id=1
log-bin=mysql-bin
binlog-ignore-db=mysql

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
在主数据库中增加复制用户并授权
mysql> grant replication slave on *.* to ‘msrslave’@’192.168.197.86’ identified by ‘slavepasswd’;
Query OK, 0 rows affected (0.00 sec)

mysql>
mysql> show master status;
+——————+———-+————–+——————+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+——————+———-+————–+——————+
| mysql-bin.000001 | 106 | | mysql |
+——————+———-+————–+——————+
1 row in set (0.00 sec)

mysql> exit

修改从服务器my.cnf配置文件
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

server-id=2
log-bin=mysql-bin
replicate-ignore-db=mysql

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
停止slave线程并配置连接master
mysql> stop slave;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> change master to master_host=’192.168.197.85′,master_user=’msrslave’,master_password=’slavepasswd’;
Query OK, 0 rows affected (0.16 sec)

mysql>

在主服务器上开启3306端口
[root@msr01 ~]# iptables -I INPUT -p tcp –dport 3306 -j ACCEPT
[root@msr01 ~]# service iptables save
iptables: Saving firewall rules to /etc/sysconfig/iptables:[ OK ]
[root@msr01 ~]#

重新启动slave服务器
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)

查看slave运行状态
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Connecting to master
Master_Host: 192.168.197.85
Master_User: msrslave
Master_Port: 3306
Connect_Retry: 60
Master_Log_File:
Read_Master_Log_Pos: 4
Relay_Log_File: mysqld-relay-bin.000001
Relay_Log_Pos: 4
Relay_Master_Log_File:
Slave_IO_Running: No
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB: mysql
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 0
Relay_Log_Space: 106
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 2013
Last_IO_Error: error connecting to master ‘msrslave@192.168.197.85:3306′ – retry-time: 60 retries: 86400
Last_SQL_Errno: 0
Last_SQL_Error:
1 row in set (0.00 sec)

ERROR:
No query specified

mysql>
在主服务器上建立新数据库并导入数据
mysql> create database linuxcache;
Query OK, 1 row affected (0.00 sec)

mysql> grant all on linuxcache.* to linuxcache;
Query OK, 0 rows affected (0.02 sec)

mysql> grant all on linuxcache.* to linuxcache@’%’;
Query OK, 0 rows affected (0.01 sec)

mysql> set password for linuxcache@’%’=password(‘lcpasswd’);
Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)
[root@msr01 ~]# mysql -u root -pmysqlpass linuxcache < linuxcache.05_06_14.sql

在从服务器上查看复制生成的数据库
[root@msr02 ~]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 13
Server version: 5.1.73-log Source distribution

Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.

mysql> show databases;
+——————–+
| Database |
+——————–+
| information_schema |
| mysql |
| linucache |
| test |
+——————–+
4 rows in set (0.00 sec)

mysql>