【转载】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>
原文地址：http://www.linuxcache.com/archives/2663
转载请注明原文地址
最新文章
- canvas arcTo()用法详解
- Linux NFS 服务部署
- 三款Javascript SPAs框架资料整理和总结
- NuGet安装和使用
- 窗体==>>初始Windows程序
- Striiv Myland 攻略
- 20160123.CCPP详解体系(0002天)
- [selenium webdriver Java]隐式的等待同步
- python面向对象进阶
- Struts2与Ajax数据交互
- CentOS下配置SS5(SOCKS5)代理服务器
- C#/.Net判断是否为周末/节假日
- 剑指offer——python【第3题】从尾到头打印链表
- ActiveMQ_6持久化
- Mysql数据备份与还原
- Python学习（五）——列表操作全透析
- 【GIS】ArcGIS JS 4.X
- splice的多种用法
- as3 air 保存文本内容的换行
- 33. Pay Gap for the Brightest Female Graduatea 最聪明的大学女毕业生面临的工资差距