MySQL 主从复制
博客已经搬家,请访问如下地址:http://www.czhphp.com
mysql的安装过程比较简单,这里略过 1.在maste主机上,配置my.cnf 在[mysqld]配置段添加如下字段中增加 log-bin=mysql-bin.log binlog-do-db=blog #要同步的数据库的名字 binlog-do-db=index #要同步的数据库的名字 server-id=1 重启mysql #service mysqld restart 进入mysql中 #mysql -u root -ppassword 增加同步帐号 mysql>grant replication slave on *.* to 'repication'@'%' identified by 'password'; mysql>use database; mysql>flush tables with read lock; mysql>show master status: --------------------------------------------------------------------- | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +------------------+----------+------------------+------------------+ | mysql-bin.000002 | 1448 | blog,index | | +------------------+----------+------------------+------------------+ 此时不要关闭此终端! 记住上表前两个字段的信息 mysql-bin.000002 和 1448 。 另开一个终端: 拿到主数据库上的“数据库快照” #tar -czvf database.tar.gz /data/database 切换到上一个终端 mysql>unlock tables; #解锁,让主数据库192.168.1.10上的数据库正常更新 2.配置slave数据库 首先创建数据库 #mysql -uroot -ppassword mysql>create database databasename #建一个与主服务器的数据库名字相同的数据库 mysql>exit; 3.在两个从数据库的[mysqld]配置段添加如下字段中增加 # 192.168.1.11 server-id=2 master-host=192.168.1.10 master-user=replication master-password=password master-connect-retry=60 replicate-do-db=blog replicate-do-db=index #192.168.1.12 server-id=3 master-host=192.168.1.10 master-user=replication master-password=password master-connect-retry=60 replicate-do-db=blog replicate-do-db=index 3.分别重启两个数据库 #service mysqld restart 4.分别进入两个从数据库中 在salve2上 #mysql -uroot -ppassword mysql>slave stop; > CHANGE MASTER TO > MASTER_HOST='192.168.1.10', > MASTER_USER='replication', > MASTER_PASSWORD='password', > MASTER_LOG_FILE='mysql-bin.000002', > MASTER_LOG_POS=1448; MYSQL>START SLAVE; 在salve2上 #mysql -uroot -ppassword mysql>slave stop; > CHANGE MASTER TO > MASTER_HOST='192.168.1.10', > MASTER_USER='replication', > MASTER_PASSWORD='password', > MASTER_LOG_FILE='mysql-bin.000002', > MASTER_LOG_POS=1448; MYSQL>START SLAVE; 在slave端 : mysql> show processlist; +----+-------------+-----------+------+---------+------------+-----------------------------------------------------------------------+------------------+ | Id | User | Host | db | Command | Time | State | Info | +----+-------------+-----------+------+---------+------------+-----------------------------------------------------------------------+------------------+ | 4 | system user | | NULL | Connect | 48 | Waiting for master to send event | NULL | | 5 | system user | | NULL | Connect | 4294923022 | Has read all relay log; waiting for the slave I/O thread to update it | NULL | | 6 | root | localhost | NULL | Query | 0 | NULL | show processlist | +----+-------------+-----------+------+---------+------------+---------------------------------------------------------- 以上信息表示同步成功! 4.同步测试过程 分别在主数据库上建立一个表,插入一条数据 在两个从数据库上看看是否更新 过程略。 |
长期招聘java,有找工作可以联系我,微信:caozhenhua1563