Think Different: Load balancing with PHP and MySql

Your enterprise portal or ERP site's server might have overloaded with huge records of your users and admin data. And you might have tried lot of options to over come this problem raised from your success eventually.
But there is a ultimate solution after optimize the architecture and code. It is Load Balancing.
Let take a look what is load balancing at a glance. Balance the overhead or load from one server to distribute into several replicated servers. So that application can manage the load very efficiently. Clustering and replication sometimes synonymous but it has also some basic and technological differences.
First we will see what is replication and how to setup in respect of MySql.
There are a number of different methods for setting up replication, and the exact method that you use will depend on how you are setting up replication, and whether you already have data within your master database.
There are some generic tasks which may be required for all replication setups:
You may want to create a separate user that will be used by your slaves to authenticate with the master to read the binary log for replication. The step is optional.
You must configure the master to support the binary log and configure a unique ID.
You must configure a unique ID for each slave that you want to connect to the Master.
Before starting a data snapshot or the replication process, you should record the position of the binary log on the master. You will need this information when configuring the slave so that the slave knows where within the binary log to start executing events.
If you already have data on your Master and you want to synchronize your slave with this base data, then you will need to create a data snapshot of your database. You can create a snapshot using mysqldump by copying the data files directly.
You will need to configure the slave with the Master settings, such as the hostname, login credentials and binary log name and positions.
Once you have configured the basic options, you will need to follow the instructions for your replication setup. A number of alternatives are provided:
If you are setting up a new MySQL master and one or more slaves, then you need only set up the configuration, as you have no data to exchange. For guidance on setting up replication in this situation,
If you are already running a MySQL server, and therefore already have data that will need to be transferred to your slaves before replication starts, have not previously configured the binary log and are able to shut down your MySQL server for a short period during the process,
If you are setting up additional slaves to an existing replication environment then you can set up the slaves without affecting the master.
Creating a User for Replication
Each Slave must connect to the Master using a standard username and password. The user that you use for this operation can be any user, providing they have been granted the REPLICATION SLAVE privilege.
You do not need to create a specific user for replication. However, you should be aware that the username and password will be stored in plain text within the master.info file. Therefore you may want to create a user that only has privileges for the replication process.
To create a user or grant an existing user the privileges required for replication use the GRANT statement. If you create a user solely for the purposes of replication then that user only needs the REPLICATION SLAVE privilege. For example, to create a user, repl, that allows all hosts within the domain mydomain.com to connect for replication:

mysql> GRANT REPLICATION SLAVE ON *.*
-> TO 'repl'@'%.mydomain.com' IDENTIFIED BY 'slavepass';

You may wish to create a different user for each slave, or use the same user for each slave that needs to connect. As long as each user that you want to use for the replication process has the REPLICATION SLAVE privilege you can create as many users as you require.
Setting the Replication Master Configuration
For replication to work you must enable binary logging on the master. If binary logging is not enabled, replication will not be possible as it is the binary log that is used to exchange data between the master and slaves.
Each server within a replication group must have a unique server-id. The server-id is used to identify individual servers within the group, and must be positive integer between 1 and (2³²)-1). How you organize and select the numbers is entirely up to you.
To configure both these options you will need to shut down your MySQL server and edit the configuration of the my.cnf or my.ini file.
You will need to add the following options to the configuration file within the [mysqld] section. If these options already exist, but are commented out, uncomment the options and alter them according to your needs. For example, to enable binary logging, using a log filename prefix of mysql-bin, and setting a server ID of 1:

[mysqld]
log-bin=mysql-bin
server-id=1
Note
For the greatest possible durability and consistency in a           replication setup using InnoDB with           transactions, you should use           innodb_flush_log_at_trx_commit=1 and           sync_binlog=1 in the master           my.cnf file.       
Note

Ensure that the skip-networking option has not been enabled on your replication master. If networking has been disabled, then your slave will not able to communicate with the master and replication will fail.

Setting the Replication Slave Configuration

The only option you must configure on the slave is to set the unique server ID. If this option is not already set, or the current value conflicts with the value that you have chosen for the master server, then you should shut down your slave server, and edit the configuration to specify the server id. For example:

[mysqld]
server-id=2

If you are setting up multiple slaves, each one must have a unique server-id value that differs from that of the master and from each of the other slaves. Think of server-id values as something similar to IP addresses: These IDs uniquely identify each server instance in the community of replication partners.

If you do not specify a server-id value, it is set to 1 if you have not defined master-host; otherwise it is set to 2. Note that in the case of server-id omission, a master refuses connections from all slaves, and a slave refuses to connect to a master. Thus, omitting server-id is good only for backup with a binary log.

You do not have to enable binary logging on the slave for replication to be enabled. However, if you enable binary logging on the slave then you can use the binary log for data backups and crash recovery on the slave, and also use the slave as part of a more complex replication topology.

Obtaining the Master Replication Information

To configure replication on the slave you must determine the masters current point within the master binary log. You will need this information so that when the slave starts the replication process, it is able to start processing events from the binary log at the correct point.

If you have existing data on your master that you want to synchronize on your slaves before starting the replication process, then you must stop processing statements on the master, obtain the current position, and then dump the data, before allowing the master to continue executing statements. If you do not stop the execution of statements then the data dump, the master status information that you use will not match and you will end up with inconsistent or corrupted databases on the slaves.

To get the master status information, follow these steps:

Start the command line client and flush all tables and block write statements by executing the FLUSH TABLES WITH READ LOCK statement:
```
mysql> FLUSH TABLES WITH READ LOCK;
```
For InnoDB tables, note that FLUSH TABLES WITH READ LOCK also blocks COMMIT operations.

Warning

Leave the client from which you issued the FLUSH TABLES statement running so that the read lock remains in effect. If you exit the client, the lock is released.
Use the SHOW MASTER STATUS statement to determine the current binary log name and offset on the master:
```
mysql > SHOW MASTER STATUS;
+---------------+----------+--------------+------------------+
| File          | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+---------------+----------+--------------+------------------+
| mysql-bin.003 | 73       | test         | manual,mysql     |
+---------------+----------+--------------+------------------+
```
The File column shows the name of the log and Position shows the offset within the file. In this example, the binary log file is mysql-bin.003 and the offset is 73. Record these values. You need them later when you are setting up the slave. They represent the replication coordinates at which the slave should begin processing new updates from the master.

If the master has been running previously without binary logging enabled, the log name and position values displayed by SHOW MASTER STATUS or mysqldump --master-data will be empty. In that case, the values that you need to use later when specifying the slave's log file and position are the empty string ('') and 4.

You now have the information you need to enable the slave to start reading from the binary log in the correct place to start replication.

If you have existing data that needs be to synchronized with the slave before you start replication, leave the client running so that the lock remains in place If you are setting up a brand new master and slave replication group, then you can exit the client and release the locks.

Creating a Data Snapshot Using `mysqldump`

One way to create a snapshot of the data in an existing master database is to use the mysqldump tool. Once the data dump has been completed, you then import this data into the slave before starting the replication process.

To obtain a snapshot of the data using mysqldump:

If you haven't already locked the tables on the server to prevent queries that update data from executing:

Start the command line client and flush all tables and block write statements by executing the FLUSH TABLES WITH READ LOCK statement:
```
mysql> FLUSH TABLES WITH READ LOCK;
```
Remember to use SHOW MASTER STATUS and record the binary log details for use when starting up the slave. The point in time of your snapshot and the binary log position must match.
In another session, use mysqldump to create a dump either of all the databases you want to replicate, or by selecting specific databases individually. For example:
```
shell> mysqldump --all-databases --lock-all-tables >dbdump.db
```
An alternative to using a bare dump, is to use the --master-data option, which will automatically append the CHANGE MASTER statement required on the slave to start the replication process.
```
shell> mysqldump --all-databases --master-data >dbdump.db
```

When choosing databases to include in the dump, remember that you will need to filter out databases on each slave that you do not want to include in the replication process.

You will need either to copy the dump file to the slave, or to use the file from the master when connecting remotely to the slave to import the data.

Creating a Data Snapshot Using Raw Data Files

If your database is particularly large then copying the raw data files may be more efficient than using mysqldump and importing the file on each slave.

However, using this method with tables in storage engines with complex caching or logging algorithms may not give you a perfect “in time” snapshot as cache information and logging updates may not have been applied, even if you have acquired a global read lock. How the storage engine responds to this depends on its crash recovery abilities.

For example, if you are using InnoDB tables, you should use the InnoDB Hot Backup tool to obtain a consistent snapshot. This tool records the log name and offset corresponding to the snapshot to be later used on the slave. Hot Backup is a non-free (commercial) tool that is not included in the standard MySQL distribution. See the InnoDB Hot Backup home page at http://www.innodb.com/hot-backup for detailed information.

Otherwise, you can obtain a reliable binary snapshot of InnoDB tables only after shutting down the MySQL Server.

To create a raw data snapshot of MyISAM tables you can use standard copy tools such as cp or copy, a remote copy tool such as scp or rsync an archiving tool such as zip or tar, or a file system snapshot tool such as dump, providing that your MySQL data files exist on a single filesystem. If you are only replicating certain databases then make sure you only copy those files that related to those tables. (For InnoDB, all tables in all databases are stored in a single file unless you have the innodb_file_per_table option enabled.)

You may want to specifically exclude the following files from your archive:

Files relating to the mysql database.
The master.info file.
The master's binary log files.
Any relay log files.

To get the most consistent results with a raw data snapshot you should shut down the server during the process, as below:

Acquire a read lock and get the master's status.
In a separate session, shut down the MySQL server:
```
shell> mysqladmin shutdown
```
Take a copy of the MySQL data files. Examples are shown below for common solutions - you need to choose only one of these solutions:
```
shell> tar cf /tmp/db.tar ./data
shell> zip -r /tmp/db.zip ./data
shell> rsync --recursive ./data /tmp/dbdata 
```
Start up the MySQL instance on the master.

If you are not using InnoDB tables, you can get a snapshot of the system from a master without shutting down the server as described in the following steps:

Acquire a read lock and get the master's status.
Take a copy of the MySQL data files. Examples are shown below for common solutions - you need to choose only one of these solutions:
```
shell> tar cf /tmp/db.tar ./data
shell> zip -r /tmp/db.zip ./data
shell> rsync --recursive ./data /tmp/dbdata 
```
In the client where you acquired the read lock, free the lock:
```
mysql> UNLOCK TABLES;
```

Once you have created the archive or copy of the database, you will need to copy the files to each slave before starting the slave replication process.

Setting Up Replication with New Master and Slaves

Setting up replication with a new Master and Slaves (i.e. with no existing data) is the easiest and most straightforward method for setting up replication.

You can also use this method if you are setting up new servers and have an existing dump of the databases that you want to load into your replication configuration. By loading the data onto a new master, the data will be automatically replicated to the slaves.

To set up replication between a new master and slave:

Configure the MySQL master with the necessary configuration properties.
Start up the MySQL master.
Setup a user,
Obtain the master status information.
Free the read lock:
```
mysql> UNLOCK TABLES;
```
On the slave, edit the MySQL configuration.
Start up the MySQL slave.
Execute the CHANGE MASTER command to set the master replication server configuration.

Because there is no data to load or exchange on a new server configuration you do not need to copy or import any information.

If you are setting up a new replication environment using the data from an existing database server, you will now need to run the dump file on the master. The database updates will automatically be propagated to the slaves:

shell> mysql -h master <>

Setting Up Replication with Existing Data

When setting up replication with existing data, you will need to decide how best to get the data

from the master to the slave before starting the replication service.

The basic process for setting up replication with existing data is as follows:

If you have not already configured the server-id and binary logging, you will need
to shut down your master to configure these options.
If you have to shut down your master database, then this is a good opportunity to take a
snapshot of the database. You should obtain the master status before
taking the database down, updating the configuration and taking a snapshot. For information on how to create a snapshot using raw data files,
If your server is already correctly configured, obtain the master status and then use mysqldump to take a snapshot take a raw snapshot of the live database using the guide in
With the MySQL master running, create a user to be used by the slave when connecting to the master during replication.
Update the configuration of the slave,
The next step depends on how you created the snapshot of data on the master.

If you used mysqldump:
1. Startup the slave, skipping replication by using the --skip-slave option.
2. Import the dump file:
```
shell> mysql <> 
```
If you created a snapshot using the raw data files:
1. Extract the data files into your slave data directory. For example:
```
shell> tar xvf dbdump.tar
```
  You may need to set permissions and ownership on the files to match the configuration of your slave.
2. Startup the slave, skipping replication by using the --skip-slave option.
Configure the slave with the master status information. This will tell the slave the binary log file and position within the file where replication needs to start, and configure the login credentials and hostname of the master. For more information on the statement required, see Section 18.1.1.10, “Setting the Master Configuration on the Slave”.
Start the slave threads:
```
mysql> START SLAVE; 
```

After you have performed this procedure, the slave should connect to the master and catch up on any updates that have occurred since the snapshot was taken.

If you have forgotten to set the server-id option for the master, slaves cannot connect to it.

If you have forgotten to set the server-id option for the slave, you get the following error in the slave's error log:

Warning: You should set server-id to a non-0 value if master_host is set; we will force server id to 2, but this MySQL server will not act as a slave.

You also find error messages in the slave's error log if it is not able to replicate for any other reason.

Once a slave is replicating, you can find in its data directory one file named master.info and another named relay-log.info. The slave uses these two files to keep track of how much of the master's binary log it has processed. Do not remove or edit these files unless you know exactly what you are doing and fully understand the implications. Even in that case, it is preferred that you use the CHANGE MASTER TO statement to change replication parameters. The slave will use the values specified in the statement to update the status files automatically.

Note

The content of master.info overrides some of the server options specified on the command line or in my.cnf. See Section 18.1.2, “Replication Startup Options and Variables”, for more details.

Once you have a snapshot of the master, you can use it to set up other slaves by following the slave portion of the procedure just described. You do not need to take another snapshot of the master; you can use the same one for each slave.

Introducing Additional Slaves to an Existing Replication Environment

If you want to add another slave to the existing replication configuration then you can do so without stopping the master. Instead, you duplicate the settings on the slaves.

To duplicate the slave:

Shut down the existing slave:
```
shell> mysqladmin shutdown 
```
Copy the data directory from the existing slave to the new slave. You can do this by creating an archive using tar or WinZip, or by performing a direct copy using a tool such as cp or rsync. Ensure you also copy the log files and relay log files.
Note

A common problem that is encountered when adding new replication slaves is that the new slave fails with a series of warning and error messages like these:
```
071118 16:44:10 [Warning] Neither --relay-log nor --relay-log-index were used; so
replication may break when this MySQL server acts as a slave and has his hostname
changed!! Please use '--relay-log=new_slave_hostname-relay-bin' to avoid this problem.
071118 16:44:10 [ERROR] Failed to open the relay log './old_slave_hostname-relay-bin.003525'
(relay_log_pos 22940879)
071118 16:44:10 [ERROR] Could not find target log during relay log initialization
071118 16:44:10 [ERROR] Failed to initialize the master info structure 
```
This is due to the fact that, if the --relay-log option is not specified, the relay log files contain the hostname as part of their filenames. (This is also true of the relay log index file if the --relay-log-index option is not used. See Section 18.1.2, “Replication Startup Options and Variables”, for more information about these options.)

To avoid this problem, use the same value for --relay-log on the new slave that was used on the existing slave. (If this option was not set explicitly on the existing slave, use existing_slave_hostname-relay-bin.) If this is not feasible, then copy the existing slave's relay log index file to the new slave and set the --relay-log-index option on the new slave to match what was used on the existing slave. (If this option was not set explicitly on the existing slave, use existing_slave_hostname-relay-bin.index.) Alternatively — if you have already tried to start the new slave (after following the remaining steps in this section) and have encountered errors like those described previously — then perform the following steps:
1. If you have not already done so, issue a STOP SLAVE on the new slave.
  
  If you have already started the existing slave again, issue a STOP SLAVE on the existing slave as well.
2. Copy the contents of the existing slave's relay log index file into the the new slave's relay log index file, making sure to overwrite any content already in the file.
3. Proceed with the remaining steps in this section.
Copy the master.info and relay.info files from the existing slave to the new slave. These files hold the current log positions.
Start the existing slave.
On the new slave, edit the configuration and the give the new slave a new unique server-id.
Start the new slave; the master.info file options will be used to start the replication process.
Setting the Master Configuration on the Slave

To set up the slave to communicate with the master for replication, you must tell the slave the necessary connection information. To do this, execute the following statement on the slave, replacing the option values with the actual values relevant to your system:
```
mysql> CHANGE MASTER TO
->     MASTER_HOST='master_host_name',
->     MASTER_USER='replication_user_name',
->     MASTER_PASSWORD='replication_password',
->     MASTER_LOG_FILE='recorded_log_file_name',
->     MASTER_LOG_POS=recorded_log_position;     
```
Note
Replication cannot use Unix socket files. You must be able to
connect to the master MySQL server using TCP/IP.
The following table shows the maximum allowable length for the string-valued options:

MASTER_HOST 60

MASTER_USER 16

MASTER_PASSWORD 32

MASTER_LOG_FILE 255
Replication Startup Options and Variables

This section describes the options that you can use on slave
replication servers. You can specify these options either on the
command line or in an option file.
On the master and each slave, you must use the
server-id option to establish a unique
replication ID. For each server, you should pick a unique positive
integer in the range from 1 to 2³² – 1, and each ID must be different from every other ID.
Example: server-id=3
Options that you can use on the master server for controlling
binary logging are described in Section 5.2.3, “The Binary Log”.
Important
Some slave server replication options are ignored if a
master.info file exists when the slave starts and contains a value for the option.
The following options are handled this way:
- --master-host
- --master-user
- --master-password
- --master-port
- --master-connect-retry
- --master-ssl
- --master-ssl-ca
- --master-ssl-capath
- --master-ssl-cert
- --master-ssl-cipher
- --master-ssl-key
The master.info file format in MySQL5.0 includes values corresponding to the SSL options.
In addition, the file format includes as its first line the number of lines in the file. you
upgrade an older server (before MySQL 4.1.1) to a newer version,the new server upgrades the
master.info file to the new format automatically when it starts. However, if you downgrade a
newer server to an older version, you should remove the first line manually before starting the
older server for the first time.
If no master.info file exists when the slave server starts, it uses the values for those
options that are specified in option files or on the command line. This occurs when you start
the server as a replication slave for the very first time, or when you have run RESET SLAVE
and then have shut down and restarted the slave.
If the master.info file exists when the slave server starts, the server uses its contents
and ignores any options that correspond to the values listed in the file. Thus, ifyou start the
slave server with different values of the startup options that correspond to values in the master.
info file, the different values have no effect, because the server continues to use the master.
info file. To use different values,you must either restart after removing the master.info file or
(preferably) use the CHANGE MASTER TO statement to reset the values while the slave is
running.
Suppose that you specify this option in your my.cnf file:
[mysqld]
```
master-host=some_host   
```
The first time you start the server as a replication slave, it reads and uses that
option from the my.cnf file. The server then records the value in the master.info file.
The next time you start the server, it reads the master host value from the master.info
file only and ignores the value in the option file. If you modify the my.cnf file to
specify a different master host of some_other_host, the change still has no effect.
You should use CHANGE MASTER TO instead.
MySQL Enterprise. For expert advice regarding master startup options subscribe to the
MySQL Enterprise Monitor. For more information, Because the server gives an existing master.
info file precedence over the startup options just described, you might prefer not to use
startup options for these values at all, and instead specify them by using the
CHANGE MASTER TO statement.
This example shows a more extensive use of startup options to configure a slave server:
```
[mysqld]
server-id=2
master-host=db-master.mycompany.com
master-port=3306
master-user=pertinax
master-password=freitag
master-connect-retry=60
report-host=db-slave.mycompany.
```
The following list describes startup options for controlling replication. Many of these
options can be reset while the server is running by using the CHANGE MASTER TO statement.
Others, such as the --replicate-* options, can be set only when the slave server starts.
- --log-slave-updates
  Normally, a slave does not log to its own binary log any updates that are received
- from a master server. This option tells the slave to log the updates performed by its
- SQL thread to its own binary log. For this option to have any effect, the slave must also be started with the --log-bin option to enable binary logging. --log-slave-updates is used when you want to chain replication servers. For example, you might want to set up replication servers using this arrangement:
```
A -> B -> C
```
  Here, A serves as the master for the slave B, and B serves as the master for the slave C. For this to work, B must be both a master and a slave. You must start both A and B with --log-bin to enable binary logging, and B with the --log-slave-updates option so that updates received from A are logged by B to its binary log.
- --log-warnings[=level]
  This option causes a server to print more messages to the error log about what it is doing. With respect to replication, the server generates warnings that it succeeded in reconnecting after a network/connection failure, and informs you as to how each slave thread started. This option is enabled by default; to disable it, use --skip-log-warnings. Aborted connections are not logged to the error log unless the value is greater than 1.
- --master-connect-retry=seconds
  
  The number of seconds that the slave thread sleeps before trying to reconnect to the master in case the master goes down or the connection is lost. The value in the master.info file takes precedence if it can be read. If not set, the default is 60. Connection retries are not invoked until the slave times out reading data from the master according to the value of --slave-net-timeout. The number of reconnection attempts is limited by the --master-retry-count option.
  --master-host=host_name
  The hostname or IP number of the master replication server. The value in master.info takes precedence if it can be read. If no master host is specified, the slave thread does not start.
  --master-info-file=file_name
  The name to use for the file in which the slave records information about the master. The default name is master.info in the data directory.
- --master-password=password
  The password of the account that the slave thread uses for authentication when it connects to the master. The value in the master.info file takes precedence if it can be read. If not set, an empty password is assumed.
- --master-port=port_number
  The TCP/IP port number that the master is listening on. The value in the master.info file takes precedence if it can be read. If not set, the compiled-in setting is assumed (normally 3306).
- --master-retry-count=count
  The number of times that the slave tries to connect to the master before giving up. Reconnects are attempted at intervals set by --master-connect-retry and reconnects are triggered when data reads by the slave time out according to the --slave-net-timeout option. The default value is 86400.
  
  These options are used for setting up a secure replication connection to the master server using SSL. Their meanings are the same as the corresponding --ssl, --ssl-ca, --ssl-capath, --ssl-cert, --ssl-cipher, --ssl-key options that are described in Section 5.5.7.3, “SSL Command Options”. The values in the master.info file take precedence if they can be read.
  --master-user=user_name
  The username of the account that the slave thread uses for authentication when it connects to the master. This account must have the REPLICATION SLAVE privilege. The value in the master.info file takes precedence if it can be read. If the master username is not set, the name test is assumed.
- --max-relay-log-size=size
  The size at which the server rotates relay log files automatically. For more information, see Section 18.4.2, “Replication Relay and Status Files”. The default size is 1GB.
  --read-only
  When this option is given, the server allows no updates except from users that have the SUPER privilege or (on a slave server) from updates performed by slave threads. On a slave server, this can be useful to ensure that the slave accepts updates only from its master server and not from clients. As of MySQL 5.0.16, this option does not apply to TEMPORARY tables.
  --relay-log=file_name
  The basename for the relay log. The default basename is host_name-relay-bin. The server creates relay log files in sequence by adding a numeric suffix to the basename. You can specify the option to create hostname-independent relay log names, or if your relay logs tend to be big (and you don't want to decrease max_relay_log_size) and you need to put them in some area different from the data directory, or if you want to increase speed by balancing load between disks.
  --relay-log-index=file_name
  The name to use for the relay log index file. The default name is host_name-relay-bin.index in the data directory, where host_name is the name of the slave server.
  --relay-log-info-file=file_name
  The name to use for the file in which the slave records information about the relay logs. The default name is relay-log.info in the data directory.
  --relay-log-purge={0|1}
  Disable or enable automatic purging of relay logs as soon as they are not needed any more. The default value is 1 (enabled). This is a global variable that can be changed dynamically with SET GLOBAL relay_log_purge = N.
  --relay-log-space-limit=size
  This option places an upper limit on the total size in bytes of all relay logs on the slave. A value of 0 means “no limit.” This is useful for a slave server host that has limited disk space. When the limit is reached, the I/O thread stops reading binary log events from the master server until the SQL thread has caught up and deleted some unused relay logs. Note that this limit is not absolute: There are cases where the SQL thread needs more events before it can delete relay logs. In that case, the I/O thread exceeds the limit until it becomes possible for the SQL thread to delete some relay logs, because not doing so would cause a deadlock. You should not set --relay-log-space-limit to less than twice the value of --max-relay-log-size (or --max-binlog-size if --max-relay-log-size is 0). In that case, there is a chance that the I/O thread waits for free space because --relay-log-space-limit is exceeded, but the SQL thread has no relay log to purge and is unable to satisfy the I/O thread. This forces the I/O thread to temporarily ignore --relay-log-space-limit.
  --replicate-do-db=db_name
  Tell the slave to restrict replication to statements where the default database (that is, the one selected by USE) is db_name. To specify more than one database, use this option multiple times, once for each database. Note that this does not replicate cross-database statements such as UPDATE some_db.some_table SET foo='bar' while having selected a different database or no database.
  Warning
  To specify multiple databases you must use multiple instances of this option. Because database names can contain commas, if you supply a comma separated list then the list will be treated as the name of a single database.
  An example of what does not work as you might expect: If the slave is started with --replicate-do-db=sales and you issue the following statements on the master, the UPDATE statement is not replicated:
```
USE prices;
UPDATE sales.january SET amount=amount+1000;
```
  The main reason for this “just check the default database” behavior is that it is difficult from the statement alone to know whether it should be replicated (for example, if you are using multiple-table DELETE statements or multiple-table UPDATE statements that act across multiple databases). It is also faster to check only the default database rather than all databases if there is no need.
  If you need cross-database updates to work, use --replicate-wild-do-table=db_name.% instead. See Section 18.4.3, “How Servers Evaluate Replication Rules”.
  --replicate-do-table=db_name.tbl_name
  Tell the slave thread to restrict replication to the specified table. To specify more than one table, use this option multiple times, once for each table. This works for cross-database updates, in contrast to --replicate-do-db. See Section 18.4.3, “How Servers Evaluate Replication Rules”.
  --replicate-ignore-db=db_name
  Tells the slave to not replicate any statement where the default database (that is, the one selected by USE) is db_name. To specify more than one database to ignore, use this option multiple times, once for each database. You should not use this option if you are using cross-database updates and you do not want these updates to be replicated. See Section 18.4.3, “How Servers Evaluate Replication Rules”.
  MySQL Enterprise. For expert advice regarding slave startup options subscribe to the MySQL Enterprise Monitor. For more information, see http://www.mysql.com/products/enterprise/advisors.html.
  An example of what does not work as you might expect: If the slave is started with --replicate-ignore-db=sales and you issue the following statements on the master, the UPDATE statement is replicated:
  USE prices;
```
UPDATE sales.january SET amount=amount+1000;
```
  Note
  In the preceding example the statement is replicated because --replicate-ignore-db only applies to the default database (set through the USE statement). Because the sales database was specified explicitly in the statement, the statement has not been filtered.
  If you need cross-database updates to work, use --replicate-wild-ignore-table=db_name.% instead. See Section 18.4.3, “How Servers Evaluate Replication Rules”.
- --replicate-ignore-table=db_name.tbl_name
  Tells the slave thread to not replicate any statement that updates the specified table, even if any other tables might be updated by the same statement. To specify more than one table to ignore, use this option multiple times, once for each table. This works for cross-database updates, in contrast to --replicate-ignore-db. See Section 18.4.3, “How Servers Evaluate Replication Rules”.
- --replicate-rewrite-db=from_name->to_name
  Tells the slave to translate the default database (that is, the one selected by USE) to to_name if it was from_name on the master. Only statements involving tables are affected (not statements such as CREATE DATABASE, DROP DATABASE, and ALTER DATABASE), and only if from_name is the default database on the master. This does not work for cross-database updates. To specify multiple rewrites, use this option multiple times. The server uses the first one with a from_name value that matches. The database name translation is done before the --replicate-* rules are tested.
  If you use this option on the command line and the “>” character is special to your command interpreter, quote the option value. For example:
```
shell> mysqld --replicate-rewrite-db="olddb->newdb" 
```
- --replicate-same-server-id
  To be used on slave servers. Usually you should use the default setting of 0, to prevent infinite loops caused by circular replication. If set to 1, the slave does not skip events having its own server ID. Normally, this is useful only in rare configurations. Cannot be set to 1 if --log-slave-updates is used. Note that by default the slave I/O thread does not even write binary log events to the relay log if they have the slave's server id (this optimization helps save disk usage). So if you want to use --replicate-same-server-id, be sure to start the slave with this option before you make the slave read its own events that you want the slave SQL thread to execute.
- --replicate-wild-do-table=db_name.tbl_name
  Tells the slave thread to restrict replication to statements where any of the updated tables match the specified database and table name patterns. Patterns can contain the “%” and “_” wildcard characters, which have the same meaning as for the LIKE pattern-matching operator. To specify more than one table, use this option multiple times, once for each table. This works for cross-database updates. See Section 18.4.3, “How Servers Evaluate Replication Rules”.
  Example: --replicate-wild-do-table=foo%.bar% replicates only updates that use a table where the database name starts with foo and the table name starts with bar.
  If the table name pattern is %, it matches any table name and the option also applies to database-level statements (CREATE DATABASE, DROP DATABASE, and ALTER DATABASE). For example, if you use --replicate-wild-do-table=foo%.%, database-level statements are replicated if the database name matches the pattern foo%.
  To include literal wildcard characters in the database or table name patterns, escape them with a backslash. For example, to replicate all tables of a database that is named my_own%db, but not replicate tables from the my1ownAABCdb database, you should escape the “_” and “%” characters like this: --replicate-wild-do-table=my\_own\%db. If you're using the option on the command line, you might need to double the backslashes or quote the option value, depending on your command interpreter. For example, with the bash shell, you would need to type --replicate-wild-do-table=my\\_own\\%db.
- --replicate-wild-ignore-table=db_name.tbl_name
  Tells the slave thread not to replicate a statement where any table matches the given wildcard pattern. To specify more than one table to ignore, use this option multiple times, once for each table. This works for cross-database updates. See Section 18.4.3, “How Servers Evaluate Replication Rules”.
  Example: --replicate-wild-ignore-table=foo%.bar% does not replicate updates that use a table where the database name starts with foo and the table name starts with bar.
  For information about how matching works, see the description of the --replicate-wild-do-table option. The rules for including literal wildcard characters in the option value are the same as for --replicate-wild-ignore-table as well.
- --report-host=slave_name
  The hostname or IP number of the slave to be reported to the master during slave registration. This value appears in the output of SHOW SLAVE HOSTS on the master server. Leave the value unset if you do not want the slave to register itself with the master. Note that it is not sufficient for the master to simply read the IP number of the slave from the TCP/IP socket after the slave connects. Due to NAT and other routing issues, that IP may not be valid for connecting to the slave from the master or other hosts.
- --report-password=password
  The account password of the slave to be reported to the master during slave registration. This value appears in the output of SHOW SLAVE HOSTS on the master server if the --show-slave-auth-info option is given.
- --report-port=slave_port_num
  The TCP/IP port number for connecting to the slave, to be reported to the master during slave registration. Set this only if the slave is listening on a non-default port or if you have a special tunnel from the master or other clients to the slave. If you are not sure, do not use this option.
- --report-user=user_name
  The account username of the slave to be reported to the master during slave registration. This value appears in the output of SHOW SLAVE HOSTS on the master server if the --show-slave-auth-info option is given.
- --show-slave-auth-info
  Display slave usernames and passwords in the output of SHOW SLAVE HOSTS on the master server for slaves started with the --report-user and --report-password options.
- --skip-slave-start
  Tells the slave server not to start the slave threads when the server starts. To start the threads later, use a START SLAVE statement.
- --slave_compressed_protocol={0|1}
  If this option is set to 1, use compression for the slave/master protocol if both the slave and the master support it. The default is 0 (no compression).
- --slave-load-tmpdir=file_name
  The name of the directory where the slave creates temporary files. This option is by default equal to the value of the tmpdir system variable. When the slave SQL thread replicates a LOAD DATA INFILE statement, it extracts the file to be loaded from the relay log into temporary files, and then loads these into the table. If the file loaded on the master is huge, the temporary files on the slave are huge, too. Therefore, it might be advisable to use this option to tell the slave to put temporary files in a directory located in some filesystem that has a lot of available space. In that case, the relay logs are huge as well, so you might also want to use the --relay-log option to place the relay logs in that filesystem.
  The directory specified by this option should be located in a disk-based filesystem (not a memory-based filesystem) because the temporary files used to replicate LOAD DATA INFILE must survive machine restarts. The directory also should not be one that is cleared by the operating system during the system startup process.
- --slave-net-timeout=seconds
  The number of seconds to wait for more data from the master before the slave considers the connection broken, aborts the read, and tries to reconnect. The first retry occurs immediately after the timeout. The interval between retries is controlled by the CHANGE MASTER TO statement or --master-connect-retry option and the number of reconnection attempts is limited by the --master-retry-count option. The default is 3600 seconds (one hour).
- --slave-skip-errors=[err_code1,err_code2,...|all]
  Normally, replication stops when an error occurs on the slave. This gives you the opportunity to resolve the inconsistency in the data manually. This option tells the slave SQL thread to continue replication when a statement returns any of the errors listed in the option value.
  Do not use this option unless you fully understand why you are getting errors. If there are no bugs in your replication setup and client programs, and no bugs in MySQL itself, an error that stops replication should never occur. Indiscriminate use of this option results in slaves becoming hopelessly out of synchrony with the master, with you having no idea why this has occurred.
  For error codes, you should use the numbers provided by the error message in your slave error log and in the output of SHOW SLAVE STATUS. Appendix B, Errors, Error Codes, and Common Problems, lists server error codes.
  You can also (but should not) use the very non-recommended value of all to cause the slave to ignore all error messages and keeps going regardless of what happens. Needless to say, if you use all, there are no guarantees regarding the integrity of your data. Please do not complain (or file bug reports) in this case if the slave's data is not anywhere close to what it is on the master. You have been warned.
  Examples:
```
--slave-skip-errors=1062,1053
--slave-skip-errors=all
```
Common Replication Administration Tasks

Once replication has been started it should execute without requiring much regular administration. Depending on your replication environment, you will want to check the replication status of each slave either periodically, daily, or even more frequently.

Checking Replication Status

The most common task when managing a replication process is to ensure that replication is taking place and that there have been no errors between the slave and the master.

The primary command for this is SHOW SLAVE STATUS which you must execute on each slave:

mysql> SHOW SLAVE STATUS\G
*************************** 1. row ***************************
        Slave_IO_State: Waiting for master to send event
           Master_Host: master1
           Master_User: root
           Master_Port: 3306
         Connect_Retry: 60
       Master_Log_File: mysql-bin.000004
   Read_Master_Log_Pos: 931
        Relay_Log_File: slave1-relay-bin.000056
         Relay_Log_Pos: 950
 Relay_Master_Log_File: mysql-bin.000004
      Slave_IO_Running: Yes
     Slave_SQL_Running: Yes
       Replicate_Do_DB:
   Replicate_Ignore_DB:
    Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
            Last_Errno: 0
            Last_Error:
          Skip_Counter: 0
   Exec_Master_Log_Pos: 931
       Relay_Log_Space: 1365
       Until_Condition: None
        Until_Log_File:
         Until_Log_Pos: 0
    Master_SSL_Allowed: No
    Master_SSL_CA_File:
    Master_SSL_CA_Path:
       Master_SSL_Cert:
     Master_SSL_Cipher:
        Master_SSL_Key:
 Seconds_Behind_Master: 0
1 row in set (0.01 sec)

The key fields from the status report to examine are:

Slave_IO_State — indicates the current status of the slave. See Section 7.5.5.5, “Replication Slave I/O Thread States”, and Section 7.5.5.6, “Replication Slave SQL Thread States”, for more information.
Slave_IO_Running — shows whether the IO thread for the reading the master's binary log is running.
Slave_SQL_Running — shows whether the SQL thread for the executing events in the relay log is running.
Last_Error — shows the last error registered when processing the relay log. Ideally this should be blank, indicating no errors.
Seconds_Behind_Master — shows the number of seconds that the slave SQL thread is behind processing the master binary log. A high number (or an increasing one) can indicate that the slave is unable to cope with the large number of queries from the master.
A value of 0 for Seconds_Behind_Master can usually be interpreted as meaning that the slave has caught up with the master, but there are some cases where this is not strictly true. For example, this can occur if the network connection between master and slave is broken but the slave I/O thread has not yet noticed this — that is, slave_net_timeout has not yet elapsed.
It is also possible that transient values for Seconds_Behind_Master may not reflect the situation accurately. When the slave SQL thread has caught up on I/O, Seconds_Behind_Master displays 0; but when the slave I/O thread is still queuing up a new event, Seconds_Behind_Master may show a large value until the SQL thread finishes executing the new event. This is especially likely when the events have old timestamps; in such cases, if you execute SHOW SLAVE STATUS several times in a relatively short peiod, you may see this value change back and forth repeatedly between 0 and a relatively large value.

On the master, you can check the status of slaves by examining the list of running processes. Slaves execute the Binlog Dump command:

mysql> SHOW PROCESSLIST \G;
*************************** 4. row ***************************
Id: 10
User: root
Host: slave1:58371
db: NULL
Command: Binlog Dump
Time: 777
State: Has sent all binlog to slave; waiting for binlog to be updated
Info: NULL

Because it is the slave that drives the core of the replication process, very little information is available in this report.

If you have used the --report-host option, then the SHOW SLAVE HOSTS statement will show basic information about connected slaves:

mysql> SHOW SLAVE HOSTS;
+-----------+--------+------+-------------------+-----------+
| Server_id | Host   | Port | Rpl_recovery_rank | Master_id |
+-----------+--------+------+-------------------+-----------+
|        10 | slave1 | 3306 |                 0 |         1 |
+-----------+--------+------+-------------------+-----------+
1 row in set (0.00 sec)

The output includes the ID of the slave server, the value of the --report-host option, the connecting port, master ID and the priority of the slave for receiving binary log updates.

Pausing Replication on the Slave

You can stop and start the replication of statements on the slave using the STOP SLAVE and START SLAVE commands.

To stop execution of the binary log from the master, use STOP SLAVE:

mysql> STOP SLAVE;

When execution is stopped, the slave does not read the binary log from the master (the IO_THREAD) and stops processing events from the relay log that have not yet been executed (the SQL_THREAD). You can pause either the IO or SQL threads individually by specifying the thread type. For example:

mysql> STOP SLAVE IO_THREAD;

Stopping the SQL thread can be useful if you want to perform a backup or other task on a slave that only processes events from the master. The IO thread will continue to be read from the master, but not executed, which will make it easier for the slave to catch up when you start slave operations again.

Stopping the IO thread will allow the statements in the relay log to be executed up until the point where the relay log has ceased to receive new events. Using this option can be useful when you want to pause execution to allow the slave to catch up with events from the master, when you want to perform administration on the slave but also ensure you have the latest updates to a specific point. This method can also be used to pause execution on the slave while you conduct administration on the master while ensuring that there is not a massive backlog of events to be executed when replication is started again.

To start execution again, use the START SLAVE statement:

mysql> START SLAVE;

If necessary, you can start either the IO_THREAD or SQL_THREAD threads individually.

MySQL replication works by having a master server where all the inserts, updates and deletes (basically any writing done) and one or more slave servers that polls the master server to replicate the database. You can only issue select queries to the slave server. You can also have multiple master servers but it won’t be covered here. You can follow this article to setup replication.

I’ll be using Round Robin to balance the load since I’ll be load balancing for a separate portion only where the same queries are used. This will equally split the load to each server (…almost). To do this in PHP, I wrote a very simple script that opens a socket. Once a host connects, it tells which database server to connect to and immediately terminates the connection.

#!/usr/bin/php -q
array(’db1.dbservers.net’,’someuser’,’somepass’),
1 => array(’db2.dbservers.net’,'anotheruser’,'anotherpass’),
/***** if you add another host (just follow the drift) *****/
// 2 => array(’db3.dbservers.net’,'yetanotheruser’,'yetanotherpass’)
);

// Loop forever
while(true)
{
// Accept anyone
$client = socket_accept($stream);
$key = key($hosts);
$reply = implode($hosts[$key], “|”);

// Move internal pointer to next host
if(next($hosts) === FALSE)
reset($hosts);

// Push response then kill the connection
socket_write($client, $reply);
socket_close($client);
}
?>

This script should be called from the command line and run like a daemon. Then we modify how we connect to the database. We connect to the “daemon” and catch the login information.

The code above can be improved further to check if a host is still up, give weights on the server depending on its hardware and other tools.

courtsy: mysql.com and blog.jploh.com

Think Different

Sunday

Load balancing with PHP and MySql

Setting the Replication Master Configuration

Setting the Replication Slave Configuration

Obtaining the Master Replication Information

Warning

Creating a Data Snapshot Using `mysqldump`

Creating a Data Snapshot Using Raw Data Files

Setting Up Replication with New Master and Slaves

Setting Up Replication with Existing Data

Note

Introducing Additional Slaves to an Existing Replication Environment

Note

Setting the Master Configuration on the Slave

Note

Replication Startup Options and Variables

Important

Pausing Replication on the Slave

No comments:

Leveraging Expertise and Resilience to Drive Success

Search This Blog

`MASTER_HOST`	60
`MASTER_USER`	16
`MASTER_PASSWORD`	32
`MASTER_LOG_FILE`	255

Sunday

Load balancing with PHP and MySql

Setting the Replication Master Configuration

Setting the Replication Slave Configuration

Obtaining the Master Replication Information

Warning

Creating a Data Snapshot Using mysqldump

Creating a Data Snapshot Using Raw Data Files

Setting Up Replication with New Master and Slaves

Setting Up Replication with Existing Data

Note

Introducing Additional Slaves to an Existing Replication Environment

Note

Setting the Master Configuration on the Slave

Note

Replication Startup Options and Variables

Important

Pausing Replication on the Slave

No comments:

Leveraging Expertise and Resilience to Drive Success

Creating a Data Snapshot Using `mysqldump`