Domain Name Hosting, Java Programming - Cheap Web Hosting Blog

Chapter 18 . Replication 537 Note You must update your applications and any other processes so that they use the new master server. If you don t do this, updates made to the old master won t be propagated to the replication partners! Unfortunately, there is no elegant solution for switching to original master server. Changing back so that the original server is again the master in the replication set involves making the original master a slave server and then following the steps in this section to change master servers on the slaves. The applications will also need to be updated to point back to the original master. Pass-through slave replication Pass-through or daisy-chain replication is a term to describe replication from a single master to a slave that then acts as a master for one or more slaves. Configuration of this type of replication is simple and similar to configuration of multiple master servers. On the slave that you want to act as a master add the following lines to the MySQL configuration file under the [mysqld] section: log-bin log-slave-updates Stop and restart the MySQL server. Add users and grants for the replication user or users that will be connecting as slaves. On each slave server configure the master as you would any other master, pointing it to the appropriate master server. Those steps are all necessary for pass-through replication. Monitoring Replication Even though replication is stable and constantly being improved, it is still another level of complication to a database. In addition, it creates yet another item that can fail and cause the administrator to lose time and sleep. Therefore, it is necessary to monitor the replication cluster to ensure that it is behaving as expected. This section examines some methods for monitoring replication including manual and semi-automated monitoring. Monitoring replication with Perl The simplest way to monitor replication is simply by typing SHOW SLAVE STATUS although this approach can quickly become a burden for a replication set of any size. Even the smallest replication set must have its status monitored fortunately, a relatively efficient method for doing so exists.
You need excellent and relaible webhost company to host your web applications? Then pay a visit to Inexpensive Web Hosting services.

536 Part V . Advanced Performance Recall the current table contents for the ecommerceexample database (shown in Figure 18-10 for convenience). These values are taken from the slave server. Figure 18-10: The current contents of the products table I now insert a value on the new master server as shown: INSERT INTO product values ( , Clear and Present Danger , Harrison Ford , 19.99 , 3 , 2 , Harrison Ford in one of the Clancy Jack Ryan series. , 2 ); It is important to note that I updated this value on the new master server. If I inserted or performed any changes on the old master server, the slave or slaves would not see them. Keep this in mind for any applications that update or work with data. If you change replication master servers, those applications must be informed of the update so they send their information to the correct master server. Examining the table contents of the product table on the slave server again reveals the new product in the database, as shown in Figure 8-11. Ignore the missing ninth value in the idcolumn. This was expected and correct behavior. I made a slight mistake behind the scenes and had to delete a row. Therefore even the auto_incrementcarried over correctly. Maybe it wasn t a mistake after all, I wanted to show that auto_increment columns even worked when changing master servers. Figure 18-11: The new values in the product table show that replication is working from the new master.
Looking for affordable and reliable webhost to host and run your business application? Then look no more and go to servlet web hosting services.

Chapter 18 . Replication 535 Figure 18-7: The current status of replication on a slave in my replication set Re-examining the status of the slave with the SHOW SLAVE STATUSstatement reveals that the change took effect as expected. The results are shown in Figure 18-8. Figure 18-8: The results from the SHOW SLAVE STATUS statement reveal that the CHANGE MASTER TO statement worked as planned. In Figure 18-8, notice the values of the Log_fileand Pos columns. Compare those to the SHOW MASTER STATUSstatement run on the new master server, as illustrated in Figure 18-9. Figure 18-9: The SHOW MASTER STATUS statement on the new master matches the values for the slave server.
You need excellent and relaible webhost company to host your web applications? Then pay a visit to Inexpensive Web Hosting services.

534 Part V . Advanced Performance Note Be sure to use a different value for each server-id. The value for every host s server-id must be unique! Multiple master replication To achieve additional peace of mind that comes from redundancy, you can configure multiple masters in a single replication set. In the background, you are really still using one master but enabling the logging of updates on one or more slaves. Should the master server fail, any of the slave(s) can act as master. To enable multiple masters in a replication set, edit the MySQL configuration file on one of the slave servers. Add the following lines to the [mysqld]section: log-bin log-slave-updates The previous lines should be in addition to the others for slave replication, including (but not necessarily limiting yourselfto) these settings: master-host= master-user= master-password=
server-id= Stop and restart the MySQL server on the slave. It should remember its position within the slave replication and also add the files you specified so it can act as a master (including the binary update log and the index log). Also, add users and privileges for the replication user on the candidate master server. For example, I m going to grant access for a user named replicate with a password of fgbpr2 to access from 192.168.1.1. GRANT FILE ON *.* TO replicate@192.168.1.1 IDENTIFIED BY fgbpr2 ; The current status of replication on the slave 192.168.1.1is shown in Figure 18-7. Note that it is using master 192.168.1.71. Assume that, for whatever reason, I have to change the master server to the candidate master. I simply issue the following statement on the slave: CHANGE MASTER TO MASTER_HOST= 192.168.1.136 ; Alter the host from 192.168.1.136for your implementation. It is important to note that when you change master servers, you may need to issue privileges so that the slave server has permission to connect to the new master. Also, you may need to make firewall or other connectivity changes to reflect the new replication master.
If you are looking for affordable and reliable webhost to host and run your business application visit our ftp web hosting services.

Chapter 18 . Replication 533 In Figure 18-5, I send the following SQL statement to put a new product on the master server: INSERT INTO product values ( , Next Friday , Ice Cube , 19.99 , 34 , 2 , Follow up to Friday. Another great Ice Cube movie. , 2 ); Figure 18-5: The INSERT statement, performed on the master server Figure 18-6 shows the same query from Figure 18-4 on the slave server. Note that the new insert that was performed on the master is propagated to the slave. Figure 18-6: The INSERT statement on the slave that propagated automatically from the master as part of the slave s replication update If a slave becomes disconnected for a normal reason, such as a SLAVE STOP command, the slave remembers its position and can catch up with any updates once it restarts. If the slave stops for an abnormal reason such as a system crash, the slave may not be able to correctly re-initialize and find its position to catch up with updates. I ve seen either outcome from situations in which the MySQL server was killed abruptly on the slave upon restart, sometimes the slave process caught up successfully, and sometimes it could not find its place. Multiple slave replication Configuring replication for multiple slaves is as simple as repeating the steps for configuring a basic replication. On the master, make sure that the replication user is granted the FILE privilege from the new slave host. Add the lines to the configuration file of the new slave host, just as you did for the first slave.
You need excellent and relaible webhost company to host your web applications? Then pay a visit to Inexpensive Web Hosting services.

532 Part V . Advanced Performance Note The values for many of the columns in Figure 18-2 will be different in your output. The important column is the Slave_running column. This column should be Yes. If the Slave_running column in your output is Noor if all values are zero or 0, then the slave didn t start. Examine the error log for MySQL on the slave usually .err for clues. For instance, when I added a third server to the replication set (for another example), I neglected to add a privilege for that user from the new host. I received the error message shown in Figure 18-3. Once I added the user to the master, all was well. Replication on the slave automatically started itself when it retried in 60 seconds. Figure 18-3: An error on the slave server shows I forgot to add the replication user account on the master. Once replication is configured, MySQL automatically transfers a copy of the databases for replication. If these are large databases, you may want to transfer the databases manually via utility commands such as ftpor scp,which allows compression of the datafiles for quicker transfer. When the initial database transfer is complete, any changes made on the master will be automatically and instantly replicated to a connected slave including inserts, updates, and deletions. For example (in Figure 18-4), I issue a simple SELECT statement on a slave server which puts seven products into the table. Figure 18-4: A listing of items in the product table on a slave server
Searching for affordable and proven webhost to host and run your servlet applications? Go to Linux Web Hosting services and you will find it.

Chapter 18 . Replication 531 databases that you want to replicate. From a shell within the database directory, issue the following command: tar -cvpf database.tar Transfer the resulting file to the slave server(s); unarchive (unzip) that file within the database directory. Your servers will now start with the same data. (This is an important step, as I show later in this chapter.) Once you ve taken the snapshot, stop the MySQL server on the master and restart it. Congratulations, you now have a master server in a replication set configured for MySQL. On the slave, the process involves adding the following lines to the MySQL configuration file, again to the [mysqld]section: master-host=192.168.1.71 master-user=replicate master-password=fgbpr2 server-id=2 Substitute the host, user, and password values appropriate for your installation. Stop the restart the MySQL server on the slave. You should now have a simple replication set running between two MySQL servers. To test the configuration, issue the following command from within the MySQL CLI on the slave: SHOW SLAVE STATUS; You should see output similar to that in Figure 18-2. Figure 18-2: Issuing the SHOW SLAVE STATUS command on a slave server should result in output similar to this if the slave is running.
From our experience, we are can tell you that you can find a reliable and cheap webhost service at Java Web Hosting services.

530 Part V . Advanced Performance A basic replication configuration Only a few options are necessary to configure a basic replication set between a master and slave in MySQL. This section examines those configuration settings. I am assuming that you ve read the section on planning and preparing for replication and have MySQL running with the same version on the participating hosts. First, you must add a user to the master server. This user serves to communicate from the slaves to the master for replication. I strongly recommend that you create a separate user for replication and grant that user only the minimum privilege necessary, the FILEprivilege. Consider this example: GRANT FILE ON *.* TO replicateuser@replicatehost IDENTIFIED BY
; Figure 18-1 illustrates this statement being executed on the server to be the master in the replication system. Remember, no need to issue a FLUSH PRIVILEGES statement when using a GRANT statement. Figure 18-1: Adding the replication user account On the master inside the MySQL configuration file, usually my.cnf or my.ini, add the following two lines to the [mysqld] section: log-bin server-id=1 The log-bin option turns on the binary log that tracks updates, the server-id option is a unique value for the replication partner in this set. The server-idvalue that you set must be unique across all partners in the replication set. For example, you cannot have two server IDs of 1 taking part in the same replication. If you have existing data in a database, you should take a snapshot of that data prior to starting replication. Create a .taror .zipfile with the database or
We recommend you use shared web hosting services, because many users agree that it is cheap, reliable and customer-satisfying webhost.

Chapter 18 . Replication 529 Option Description master-password Password to use for replication. master-port Port to use for replication, default 3306 or the MYSQL_PORT variable. master-connect-retry Value in seconds to wait before attempting to reconnect in the event of a non-fatal error. master-ssl Enables replication over SSL. master-ssl-key Name of keyfile to use for SSL-enabled replication. master-ssl-cert Name of certificate to use for SSL-enabled replication. master-info-file Location and name of file that contains replication information. report-host Special IP to report as when performing replication. report-port Port number to report to master for replication. replicate-do-table Name of table to replicate. replicate-ignore-table Name of table to ignore for replication. replicate-wild-do-table Wildcard matching for tables to replicate. replicate-wild-ignore= Wildcard matching for tables to exclude from table replication. replicate-do-db Name of database to include in replication. replicate-ignore-db Name of database to exclude from replication. replicate-rewrite-db Name of database to replicate and then rename on slave. RESET SLAVE Causes slave to restart replication from beginning of logfile. server-id Unique integer to identify the server in a replication set. slave-skip-errors Ignore errors that would normally be fatal. Use with care. SHOW NEW MASTER Used to assist in changing the master server for a replication. This statement is rarely used and requires additional syntax. SHOW SLAVE STATUS Gives information on the slave replication process. SLAVE START Starts the slave process, if it hasn t been started. SLAVE STOP Stops the slave process. skip-slave-start Prevents the replication slave process from starting with the MySQL server. slave-read-timeout Seconds before timing out the slave process if no activity is received.
Note: If you are looking for cheap and reliable webhost to host and run your mysql application check mysql web server services.

528 Part V . Advanced Performance Table 18-1 Options For Master Server In MySQL Replication Option Description binlog-ignore-db Replication will be done on all databases except database specified. log-bin Sends replication updates to logfile in binary format. log-bin-index Sets the filename of the file to track which log number is current. PURGE MASTER LOGS TO Deletes replication logs. RESET MASTER Purges replication-related logfiles and restarts replication. server-id Unique integer value of server within a replication set. SET SQL_LOG_BIN=0 Stops replication or updating of the binary log. SET SQL_LOG_BIN=1 Starts replication or updating of the binary log. SHOW BINLOG EVENTS Used for debugging, this statement shows items in the binary log. SHOW MASTER LOGS Gives information on the binary logs residing on the master. SHOW MASTER STATUS Gives information on the master replication process. binlog-do-db Replication will be performed only on updates to database specified. The options and settings applicable for a slave server are shown in Figure 18-2. As in Table 18-1, SQL statements are shown in all uppercase. Table 18-2 Options For Slave Server In MySQL Replication Option Description CHANGE MASTER TO Changes the master that a slave replicates with. LOAD DATA FROM MASTER Transfers a snapshot copy of the data from master to begin replication. LOAD TABLE FROM MASTER Transfers a specific table from a master server. log-slave-updates Enables logging of updates on slave server. master-host The IP or hostname of the master server. master-user Username to use for replication.
Check Tomcat Web Hosting services for best quality webspace to host your web application.