Symfony Doctrine MySQL Replication

Symfony Doctrine MySQL Replication

网站架构加入mysql读写分离，是流行的做法。下面记录一下如何如何让采用symfony框架的产品加入mysql读写分离，我们使用 doctrine实现数据操作。

在database.yml里输入：

1. all:
2.   master:
3.     class: sfDoctrineDatabase
4.     param:
5.     dsn: ‘mysql:host=localhost;dbname=mydb’
6.     username: master
7.     password: master
8.     option: charset=utf8
9.   slave:
10.     class: sfDoctrineDatabase
11.     param:
12.     dsn: ‘mysql:host=localhost;dbname=mydb’
13.     username: slave
14.     password: slave
15.     option: charset=utf8

master和slave的前后顺序取决于你的具体需求，doctrine数据模型默认读取最后一个，由于一般网站大部分操作都是读操作，所以我们 把slave写在后面了。

具体数据操作代码如下：

1. //插入
2. $add = new Tblusers();
3. $add->username = “username”;
4. $add->password = “passwd”;
5. $add->save(Doctrine_Manager::getInstance()->getConnection(‘master’));
6. //编辑
7. $query = Doctrine_Query::create(Doctrine_Manager::getInstance()->getConnection(‘master’))
8. ->update(‘Tblusers a’)
9. ->set(‘a.username = ?’,”username”)
10. ->where(‘a.uid = ?’,2)
11. ->execute();
12. //显示
13. $rs = Doctrine_Query::create()
14. ->from(‘Tblusers a’)
15. ->where(‘a.username = ?’,”username”)
16. ->fetchArray();
17. //删除
18. $del = Doctrine_Query::create(Doctrine_Manager::getInstance()->getConnection(‘master’))
19. ->delete()
20. ->from(‘Tblusers a’)
21. ->where(‘a.username = ?’,”username”)
22. ->execute();

除此以外，Doctrine官方网站上的cookbook还介绍了通过添加自定义类（继承自Doctrine_Query）来实现。

 

 

mysql服务器的主从配置，这样可以实现读写分离，也可以在主库挂掉后从备用库中恢复

需要两台机器，安装mysql，两台机器要在相通的局域网内

主机A: 192.168.1.100

从机B:192.168.1.101

可以有多台从机

1、先登录主机 A

mysql>GRANT REPLICATION SLAVE ON *.* TO ‘backup’@’192.168.1.101‘ IDENTIFIED BY ‘123456’;

赋予从机权限，有多台丛机，就执行多次

2、 打开主机A的my.cnf，输入

server-id               = 1    #主机标示，整数
log_bin                 = /var/log/mysql/mysql-bin.log   #确保此文件可写
read-only              =0  #主机，读写都可以
binlog-do-db         =test   #需要备份数据，多个写多行
binlog-ignore-db    =mysql #不需要备份的数据库，多个写多行

3、打开从机B的my.cnf，输入

server-id               = 2
log_bin                 = /var/log/mysql/mysql-bin.log
master-host     =192.168.1.100
master-user     =backup
master-pass     =123456
master-port     =3306
master-connect-retry=60 #如果从服务器发现主服务器断掉，重新连接的时间差(秒)
replicate-do-db =test #只复制某个库
replicate-ignore-db=mysql #不复制某个库

4、同步数据库

不用太费事，只把主从库都启动即可自动同步，如果不嫌麻烦的话可以把主库的内容导出成SQL，然后在从库中运行一遍

5、先重启主机A的mysql，再重启从机B的mysql

6、验证

在主机A中，mysql>show master statusG;

在从机B中，mysql>show slave statusG;

能看到大致这些内容

File: mysql-bin.000001
Position: 1374
Binlog_Do_DB: test
Binlog_Ignore_DB: mysql

可以在主机A中，做一些INSERT, UPDATE, DELETE 操作，看看主机B中，是否已经被修改.

 

 

MYSQL Replication

Why use Database Replication?

1. Provide a backup in case of a hard disk failure
2. Quick switch over for master server downtime
3. Provide optimization as a read only database apart from a transaction write intensive database

Step to set up:

I.  Setup the master database

1. Edit the config file my.cnf (usualy locate on the /etc folder)

At the [mysqld] block add the follow lines.

log-bin=mysql-bin
server-id=1
innodb_flush_log_at_trx_commit=1
sync_binlog=1

server-id =1 will set the server as a master node. log-bin=mysql-bin sets up the log file for binary log which will be used to sync with the slave database node. After adding the lines, restart mysql service.

usually running /sbin/service mysql-server restart, depended on your server setup. Check the mysql log file [default location: /var/log/mysqld.log] if everything is fine or if there is any errors.You also can run the following command to find out more information about your mysql, e.g. where is the log file, and data directory.

#ps aux| grep mysqld

2. Set up replication user:

Login to mysql console as root and create a user with replication privilege.

GRANT REPLICATION SLAVE ON *.* TO whoever_user@’123.45.56.%’ IDENTIFIED BY ‘secret_password’;

3. Lock tables to retrieve ‘Master Server Status’

This is to prevent writing to the master database before getting the log position.

Run the command on the mysql console to lock tables

# FLUSH TABLES WITH READ LOCK;

4. Get Master Status

Run the following command on the mysql console to get the Master Status

# Show Master Status

Record the values for setting up the slave database later.

5. Unlock the master database

On the mysql console, issue this command – # unlock tables;

Now, let’s set up the slave database

1. Configurate the my.ini with this values

enter server-id=2 in the [mysqld] area and save the file

2. restart mysqld for the slave database

usually running /sbin/service mysql-server restart, depended on your server setup. Check the mysql log file [default location: /var/log/mysqld.log] if everything is fine or if there is any errors.

3. Login into the Linux server and at the shell console, run the command – #mysql -uroot -p

At the prompt, enter the password for mysql root user

4. Configurate the slave’s master database info so that it will run the slave replication getting the data from the master database’s bin-log

CHANGE MASTER TO
MASTER_HOST=’[place your master database ip here]’,
MASTER_USER=’user’,
MASTER_PASSWORD=’password’,
MASTER_PORT=3306,
MASTER_LOG_FILE=’mysql-bin.000008′,
MASTER_LOG_POS=462734485,
MASTER_CONNECT_RETRY=10;

If you look at my image, those are the informations I recorded on the Log Position and log file name

5. start the slave by running this command on the mysql console

Start slave;

6. Monitor the mysql log file if there is any connection issue or error.

Helpful commands:

show slave status: this will show the status of the slave

show master status: this will show the master database status

stop slave : this will stop the replication

reset slave: this will reset the slave

reset master: this will reset the master database configuration at the slave database

 

–end

原地址：http://chenpeng.info/html/331