Mysql的主从复制读写分离--简单篇

loing

Mysql基础拓扑图：

Mysql环境准备：
一台mysql主服务器（安装mysql）
两台mysql从服务器(安装mysql)
一台mysql代理(安装amoeba和java)
一台mysql客户端（mysql客户端）

部署前先关闭所有的iptables，selinux

Mysql的主从复制读写分离所需安装包：
cmake-2.8.6.tar.gz   

mysql-5.5.22.tar.gz
amoeba-mysql-binary-2.2.0.tar.gz
jdk-7u65-linux-x64.tar.gz
jdk-6u14-linux-x64.bin
ncurses-devel


部署一个时间服务器来进行时间同步：
yum -y install ntp

编辑配置文件添加如下行：
server 127.127.1.0
fudge 127.127.1.0 stratum 8

重启服务:

客户端安装软件同步：
yum -y install ntp-date
/usr/sbin/ntpdate 99.99.99.22

---

安装mysql独特的编译安装软件cmake：
tar zxf /root/cmake-2.8.6.tar.gz -C /root/
cd /root/cmake-2.8.6
./configure && gmake && gmake install


安装mysql依赖包：
yum -y install ncurses-devel

编译安装mysql

tar zxf /root/mysql-5.5.22.tar.gz -C /usr/src
cd /usr/src/mysql-5.5.22
cmake -DCMAKE_INSTALL_PREFIX=/usr/local/mysql \
-DSYSCONFDIR=/etc \
-DDEFAULT_CHARSET=utf8 \
-DDEFAULT_COLLATION=utf8_general_ci \
-DWITH_EXTRA_CHARSETS=all
make && make install

授权数据库用户：
useradd -M -s /sbin/nologin mysql -g mysql
chown -R mysql:mysql /usr/local/mysql


初始化数据库：

/usr/local/mysql/scripts/mysql_install_db \
--user=mysql \
--basedir=/usr/local/mysql \
--datadir=/usr/local/mysql/data/

优化数据库
cp -rf /usr/src/$MY_Q/support-files/my-medium.cnf /etc/my.cnf
cp -rf /usr/src/$MY_Q/support-files/mysql.server /etc/init.d/mysqld
chmod +x /etc/init.d/mysqld
chkconfig --add mysqld
chkconfig mysqld on

启动数据库并设置密码：

echo "PATH=$PATH:/usr/local/mysql/bin" >>/etc/profile
source /etc/profile
/etc/init.d/mysqld start
mysqladmin -uroot password '123123'

---

配置Mysql主服务器
vim /etc/my.cnf
server-id       = 11   #改

log-bin=master-bin  #改

log-slave-updates=true   #添加

重启服务：
/etc/init.d/mysqld restart

登陆Mysql给服务器授权

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22

[iyunv@cjl ~]# mysql -uroot -p123123 Welcome to the MySQL monitor.  Commands end with ; or \g. Your MySQL connection id is 4 Server version: 5.5.22-log Source distribution Copyright (c) 2000, 2011, 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>grant replication slave on *.* to 'myslave'@'99.99.99.%' identified by '123123'; Query OK, 0 rows affected (0.00 sec) mysql> flush privileges; Query OK, 0 rows affected (0.00 sec) mysql> show master status ; +-------------------+----------+--------------+------------------+ | File              | Position | Binlog_Do_DB | Binlog_Ignore_DB | +-------------------+----------+--------------+------------------+ | master-bin.000002 |      336 |              |                  | +-------------------+----------+--------------+------------------+ 1 row in set (0.00 sec)

从服务器的数据库与主服务器一致，不需要配置任何命令。

配置第一个从服务器添加如下行：
vim /etc/my.cnf
server-id       = 22   #改
relay-log=relay-log-bin   #添加
relay-log-index=slave-relay-bin.index  #添加

重启服务：
service mysqld restart

配置数据库：

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18

[iyunv@cjl ~]# mysql -uroot -p123123 Welcome to the MySQL monitor.  Commands end with ; or \g. Your MySQL connection id is 4 Server version: 5.5.22-log Source distribution Copyright (c) 2000, 2011, 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> change master to master_host='99.99.99.22',master_user='myslave',master_password='123123',master_log_file='master-bin.000001',master_log_pos=336; start slave； show slave status\G;              Slave_IO_Running: Yes             Slave_SQL_Running: Yes

配置第二个从服务器添加如下行：
vim /etc/my.cnf
server-id       = 33  #改
relay-log=relay-log-bin   #添加
relay-log-index=slave-relay-bin.index  #添加

重启服务：
/etc/init.d/mysqld restart

配置mysql数据库：

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18

[iyunv@cjl ~]# mysql -uroot -p123123 Welcome to the MySQL monitor.  Commands end with ; or \g. Your MySQL connection id is 4 Server version: 5.5.22-log Source distribution Copyright (c) 2000, 2011, 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> change master to master_host='99.99.99.22',master_user='myslave',master_password='123123',master_log_file='master-bin.000001',master_log_pos=336; start slave； show slave status\G;              Slave_IO_Running: Yes             Slave_SQL_Running: Yes

验证主从复制的效果
登陆主服务器，创建数据库：

从服务器查看：

---

实现Mysql的读写分离（待续）：