So far I have been trying to do what is called a database replication, the key is to have my blog’s data copied over a different server on a seamless fashion. I have my northtec computer currently running a MySQL database and working as a webserver on my apartment and my home. They were on two different states of Mexico and I want them to automatically sync up the database.This is called replication, according to the MySQL database these are the benefits of replication:
Replication offers benefits for robustness, speed, and system administration: * Robustness is increased with a master/slave setup. In the event of problems with the master, you can switch to the slave as a backup. * Better response time for clients can be achieved by splitting the load for processing client queries between the master and slave servers. SELECT queries may be sent to the slave to reduce the query processing load of the master. Statements that modify data should still be sent to the master so that the master and slave do not get out of synchrony. This load-balancing strategy is effective if non-updating queries dominate, but that is the normal case. * Another benefit of using replication is that you can perform database backups using a slave server without disturbing the master. The master continues to process updates while the backup is being made. See Section 5.9.1, Ã¢â‚¬Å“Database BackupsÃ¢â‚¬Â.
My main target is having the live, remote backup and quick turnover was what got me into getting a replicative database. However i haven’t been able to achieve a good replication and start from there. The setup sounds to be interesting but some holes and doubts come to be in that tutorial. My guess is that I should look for another tutorial to get the database working. So I worked with the HowToForge tutorial which was very simply explained on one side, however on the other they missed at some point making sure the user was either on the MASTER or the SLAVE server.The tutorial basically went on like this:
- activating network on mysql so the remote server can login.
- verify that you can login from the mysql-client on what would be the slave server to make sure there are no port closed.
- Edit the Master’s my.cf file with the database, the binary-log and the server id, which is the master ID.
- Restart MySQL to apply changes
- Create mysql’s slave user. UNLOCK TABLES.
We need to manually sync the database via a dump for the first time however we need parameter, with a mysqldump. The next step is to create the slave database as root which threw me off a bit since we will also have a slave user on the master and I think they need to be the same. The tutorial says:
On the slave we first have to create the database exampledb:mysql -u root -pEnter password:CREATE DATABASE exampledb;quit;If you have made an SQL dump of exampledb on the master and have transferred it to the slave, then it is time now to import the SQL dump into our newly created exampledb on the slave.
On that note I notice that they are still rooting on the slave database. We need to configure the my.cf file so we can get the connectivity automatically. The key is the assignment of the user. CHANGE MASTER TO MASTER_HOST=’192.168.0.100′, MASTER_USER=’slave_user’, MASTER_PASSWORD=”, MASTER_LOG_FILE=’mysql-bin.006′, MASTER_LOG_POS=183; .My issue had to do with the LOAD DATA FROM MASTER which seems to be too slow to work properly. I hope the MySQL tutorial has a more cmplete one.