How to achieve separation of database reading and writing in phalapi

Read-write separation is a commonly used solution in many large-scale projects. It can solve the database bottleneck problem when a large number of read operations occur. So how to build a database distribution with separation of reading and writing, and how does the terminal perform separation of reading and writing? This article explains the implementation of reading and writing separation based on phalapi from examples. I hope it will be helpful to everyone.

1. Separation of reading and writing

We face concurrent problems all the time in our lives, such as traffic problems during rush hours after get off work. People use traffic lights to solve part of the problem (but it is very painful) It’s the same as blocking when querying, doing the update operation in 10 seconds, and doing the select operation in 10 seconds), then people have thought of a method of elevated, the elevated route is consistent with the normal route (the data is the same), elevated points There are two roads and cars on one road cannot go to the other road (separation of reading and writing). This solves our conventional concurrency problems.

In fact, it seems that the above solution is better, but it is not. In this way, there are two roads but their widths are actually different. Here we call one the writing road and the other the reading road. If we want to make a reading road and double its width, it is very simple. (that is, multiple slave libraries), but we can establish a writing path, but it is more difficult to widen this writing path, and we generally do not do this.

1.1 mysql master-slave Configuration

I used Alibaba Cloud's multi-read library service. Here is a quote from Baidu's experience in mysql master-slave processing

------ -------------------------------------------------- --------------------------------------------------

MYSQL [Master-slave configuration of multiple instances of one host]

1. Create an account

Enter the mysql database and create a slave database for backup Account

mysql>GRANT REPLICATION SLAVE ON *.* TO 'slave_test'@'10.19.194.57' IDENTIFIED BY 'Password@123456';
mysql>FLUSH PRIVILEGES;   ---刷新

For details of this command, please refer to the grant create user command

2. If there is already an instance running (because mysql has been installed at the beginning, there is a default), then start another instance as follows:

(1)shell>cd /usr/local/mysql/

(2)shell>mkdir -pv /data/ mysql2

（3）shell>chown -R mysql.mysql /data/mysql2

（4）shell>./scripts/mysql_install_db --user=mysql --datadir=/data/ mysql2 This is another directory. This step means initializing the directory and creating an instance

3. Configure /etc/my.cnf. The configuration method is as follows

[mysqld_multi]
mysqld = /usr/local/mysql/bin/mysqld_safe
mysqladmin = /usr/local/mysql/bin/mysqladmin
user = root
[mysqld] ## 本来mysql配置
server-id = 1
port = 3306
socket = /data/mysql/mysql.sock
datadir = /data/mysql
pid-file = /data/mysql/wzj.pid
log-error = /data/mysql/wzj.err
log-bin = /data/mysql/log/bin-log
log-bin-index = /data/mysql/log/mysql-bin.index
user = mysql
[mysqld2]  ##创建实例配置
server-id = 2
port = 3307
socket = /tmp/mysql2.sock
datadir = /data/mysql2/                         //mysql2存储数据库的地方，也就是实例
pid-file = /data/mysql2/mysql2.pid       //mysql2的pid文件记录
log-error = /data/mysql2/mysql2.err     //为错误日志文件的地方
user = mysql
#master-host =10.19.194.57
#master-user ='wzj_slave_test'
#master-pass ='Password@123456'
#master-port =3306
#master_connect_retry=60
#replicate-do-db =mysql_wzj
#replicate-ignore-db=mysql--------该段已注释，因为新版本中，需要用CHANGE MASTER TO 命令去设置

[Note]: The above files Directories are all created by the system, not manually. After setting the configuration file, restart mysql, and the file will be automatically created

4. Restart, and then you can use the msyql_multi command to start Example, check the status of the master database again, where Binlog_Do_DB indicates the database to be backed up, and Binlog_Ignore_DB indicates the database not to be backed up

shell>/etc/rc.d/init.d/mysqld start/stop/restart
shell>/usr/local/mysql/bin/mysqld_multi start 1-2  启动实例 （1-2表示启动1、2两个实例）
shell>mysql master： mysql -P 3306 -u root -p -S /data/mysql/mysql.sock (密码：1)
mysql>show master status; or show master status \G;   显示主库的状态，其中File 和Position 数值要记住，下面的mysql语句要用到，用于设置从库

5. Enter the slave database and set some parameters of the slave database

shell>slave:mysql -P 3307 -u root -p -S /tmp/mysql2.sock （密码：回车）   ---另外开个远程
mysql>CHANGE MASTER TO
mysql> MASTER_HOST='10.19.194.57',  ---主库的地址
mysql>MASTER_USER='wzj_slave_test',  ---主库用户
mysql>MASTER_PASSWORD='Password@123456',   ---主库的密码
mysql>MASTER_LOG_FILE='bin-log.000013' ,  ---此参数为master status中的File值
mysql>MASTER_LOG_POS=120;   ---此数值为Position数值

6. After setting up, run start slave. If the red line in the figure below is YES, it is successful. Otherwise, check whether the configuration file is correct or incorrect.

slave_io_running  ：yes
slave_sql_running :yes

7. You can check the mysql process information under the slave library

mysql>show processlist;

8. Test

Create a new database on the main library, and then check whether there is synchronization in the slave library ~

--------------------------------------------- -------------------------------------------------- -------

1.2 Start to realize the

idea. We have finished talking about it. I believe everyone has understood it more or less, but what should we do? To implement the read-write separation operation in phalapi

1. First we need to initialize a database connection. We need to create a configuration file read.php, like dbs.php, which only connects the configuration items of this reading library, and Register

//读库
DI()->read = function (){
    $debug = !empty($_GET['__sql__']) ? true : false;
    return new PhalApi_DB_NotORM(DI()->config->get('read'), $debug);
};

2. Modify getORM in the PhalApi_Model_NotORM file

/**
 * 快速获取ORM实例，注意每次获取都是新的实例
 */
protected function getORM($id = NULL){
    $table = $this->getTableName($id);
    if($id == 'read'){
        return DI()->read->$table;
    }
    return DI()->notorm->$table;
}

3. When we use $this->getORM('read') in the model layer, we will use read Library, using $this->getORM() will use the write library by default

//select操作
$this->getORM('read')->select('*')->where('aId', $aId)->fetchAll();
//insert,update,delete操作
$this->getORM()->insert();

2. Multi-library processing

Some people will ask why multi-library processing is needed. All tables are placed Is it good to have a library soon?

When I originally refactored other people’s outsourcing projects, I encountered a library with more than 100 tables, no notes, no documents, and almost impossible to read. When I made a larger one myself, During the project, I also made some considerations to decompose the library into sub-libraries according to the business, which can very well avoid the problem of too many tables and confusing structures, and we can transplant any sub-library out at any time. When I host a mysql To reduce a lot of pressure, I can decompose the sub-library on this mysql into multiple mysql to achieve pressure offloading.

After reading the above introduction, I believe everyone understands the benefits of doing so. , but how to implement it? Smart people may be thinking about registering an additional database connection as above. In fact, everyone is half right. We need to register a connection, but we need a unified specification to use this type of database connection. Operation

1. Initialize the database connection, copy the same copy of dbs.php to configure developers.php to connect to the developer library

//开发者库
DI()->developers = function (){
    $debug = !empty($_GET['__sql__']) ? true : false;
    return new PhalApi_DB_NotORM(DI()->config->get('developers'), $debug);
};

2. Correct PhalApi_Model_NotORM Modify getORM in the file

/**
 * 快速获取ORM实例，注意每次获取都是新的实例
 */
protected function getORM($id = NULL){
    $table = $this->getTableName($id);
    if($id == 'developers'){
        return DI()->developers->$table;
    }
    return DI()->notorm->$table;
}

3. Before we did not create our public General file, we were building such a structure

--General                   //公用目录
    --developers                //通用项目库目录
        --Domain                //项目库Domain层
        --Model             //项目库Model层

然后我们在这里的Model进行的操作都是使用$this->getORM('developers')进行操作

这样规范的好处就是在与共享和模块化 让正常的一个Model层不会使用到任何不同库的操作,我们多个项目用到其他的一个库可以进行代码复用,

相关推荐：

详解MySQL的主从复制、读写分离、备份恢复

php实现带读写分离功能的MySQL类完整实例

phalapi-缓存的使用以及redis拓展

The above is the detailed content of How to achieve separation of database reading and writing in phalapi. For more information, please follow other related articles on the PHP Chinese website!