Creating a master/slave database configuration

To set up a master/slave database, perform the following steps from the command line.

The first step is to determine which address the MySQL server is bound to. To do this, locate the line of the MySQL configuration file that contains the bind-address parameter:
```
bind-address            = 127.0.0.1
```
This IP address will be set to whichever IP address the master server uses.
Next, uncomment the line of the MySQL configuration file that contains the server-id, which is most probably located at /etc/my.cn or /etc/mysql/mysql.conf.d/mysqld.cnf.
The Unix sed command can perform this easily:
```
$ sed -i s/#server-id/server-id/g  /etc/mysql/my.cnf
```
Tip
The /etc/mysql/my.cnf string will need to be substituted with the correct filename.
Uncomment the line of the MySQL configuration file that contains the server-id:
```
$ sed -i s/#log_bin/log_bin/g  /etc/mysql/my.cnf
```
Tip
Again, the /etc/mysql/my.cnf string will need to be substituted with the correct filename.
Now, MySQL needs to be restarted. You can do this using the following command:
```
$ sudo service mysql restart
```

The following placeholders should be substituted with the actual values:

MYSQLUSER
MYSQLPASSWORD
MASTERDATABASE
MASTERDATABASEUSER
MASTERDATABASEPASSWORD
SLAVEDATABASE
SLAVEDATABASEUSER
SLAVEDATABASEPASSWORD

Master server set up

The following are the steps for setting up a master server:

Grant permission to the slave database user:

$ echo  "GRANT REPLICATION SLAVE ON *.* TO 'DATABASEUSER'@'%' IDENTIFIED BY 'DATABASESLAVEPASSWORD';" | mysql -u MYSQLUSER -p"MYSQLPASSWORD"

Next, the permissions must be flushed using the following command:

$ echo  "FLUSH PRIVILEGES;" | mysql -u MYSQLUSER -p"MYSQLPASSWORD"

Next, switch to the master database using the following command:

$ echo  "USE MASTERDATABASE;" | mysql -u MYSQLUSER -p"DATABASEPASSWORD"

Next, flush the tables using the following command:

$ echo  "FLUSH TABLES WITH READ LOCK;" | mysql -u MYSQLUSER -p"MYSQLPASSWORD"

Dump the master database using the following command:

$ mysqldump -u root -p"MYSQLPASSWORD"  --opt "MASTERDATABASE" > dumpfile.sql

Unlock the tables using the following command:

$ echo  "UNLOCK TABLES;" | mysql -u MYSQLUSER -p"MYSQLPASSWORD"

The following are the steps for setting up a slave server:

On the slave server, create the slave database using the following command:

$ echo  "CREATE DATABASE SLAVEDATABASE;" | mysql -u MYSQLUSER -p"MYSQLPASSWORD"

Import the dump file created from the master database using the following command:
```
$ mysql -u MYSQLUSER -p"MYSQLPASSWORD"  "MASTERDATABASE" < dumpfile.sql
```
Now, the MySQL configuration file uses server-id 2:
```
server-id            = 2
```

In the MySQL configuration file, two lines should be uncommented, as follows:

#log_bin			= /var/log/mysql/mysql-bin.log
expire_logs_days	= 10
max_binlog_size   = 100M
#binlog_do_db		= include_database_name

You will get the following result:

log_bin			= /var/log/mysql/mysql-bin.log
expire_logs_days	= 10
max_binlog_size    = 100M
binlog_do_db		= include_database_name

Additionally, the following line needs to be added below binglog_do_db:
```
relay-log                = /var/log/mysql/mysql-relay-bin.log
```
Now, MySQL needs to be restarted using the following command:
```
$ sudo service mysql restart
```
Finally, the master password is set. The master log file and positions are to be set to the filename and the position recorded in step 5. Run the following command:
```
MASTER_PASSWORD='password', MASTER_LOG_FILE='FILENAME', MASTER_LOG_POS= POSITION;
```

..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.