# MySQL Replication

<p class="callout warning">\*\*\* TESTED FOR CENTOS 7 \*\*\*</p>

<p class="callout info">NEED TO HAVE PORT 3306 OPENED! -- MASTER = 10.1.2.117, SLAVE = 10.1.2.118</p>

#### Master:

```
vi /etc/my.cnf
```

> \[mysqld\]  
> bind-address = 10.1.2.117  
> server-id = 1  
> log\_bin = /var/lib/mysql/mysql-bin.log  
> binlog-do-db=mydb  
> datadir=/var/lib/mysql  
> socket=/var/lib/mysql/mysql.sock  
> symbolic-links=0  
> sql\_mode=NO\_ENGINE\_SUBSTITUTION,STRICT\_TRANS\_TABLES  
>   
> \[mysqld\_safe\]  
> log-error=/var/log/mysqld.log  
> pid-file=/var/run/mysqld/mysqld.pid

```
systemctl restart mysql
```

<p class="callout info">If new server without db create before you grant permissions, if you already have a db running keep reading to see how you can move your db to slave.</p>

```
GRANT REPLICATION SLAVE ON *.* TO 'slave_user'@'%' IDENTIFIED BY 'password';
FLUSH PRIVILEGES;
USE mydb;
FLUSH TABLES WITH READ LOCK;
```

Note down the position number you will need it on a future command.

```
SHOW MASTER STATUS;

+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 |      665 | newdatabase  |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
```

```
mysqldump -u root -p --opt mysql > mysql.sql
```

```
UNLOCK TABLES;
```

#### Slave:

```
CREATE DATABASE mydb;
```

Now import the DB from the MASTER

```
mysql -u root -p mydb < /path/to/mydb.sql
```

vi /etc/my.cnf

> \[mysqld\]  
> server-id = 2  
> relay-log = /var/lib/mysql/mysql-relay-bin.log  
> log\_bin = /var/lib/mysql/mysql-bin.log  
> binlog-do-db=mydb  
> datadir=/var/lib/mysql  
> socket=/var/lib/mysql/mysql.sock  
> symbolic-links=0  
> sql\_mode=NO\_ENGINE\_SUBSTITUTION,STRICT\_TRANS\_TABLES
> 
> \[mysqld\_safe\]  
> log-error=/var/log/mysqld.log  
> pid-file=/var/run/mysqld/mysqld.pid

<p class="callout info">To add more DB's create another line with the db name: binlog-do-db=mydb2 in my.cnf</p>

```
systemctl restart mysql
```

```
CHANGE MASTER TO MASTER_HOST='10.1.2.117',MASTER_USER='slave_user', MASTER_PASSWORD='password', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=665;
START SLAVE;
SHOW SLAVE STATUS\G
```

<p class="callout warning">Look at **Slave\_IO\_State** &amp; **Slave\_IO\_Running** &amp; **Slave\_SQL\_Running** &amp; make sure **Master\_LOG** and **Read\_Master\_Log\_Pos** matches the master.</p>

[![Screenshot-20170723195816-798x255.png](https://wiki.myhypervisor.ca/uploads/images/gallery/2017-12-Dec/scaled-840-0/Screenshot-20170723195816-798x255.png)](https://wiki.myhypervisor.ca/uploads/images/gallery/2017-12-Dec/Screenshot-20170723195816-798x255.png)

If there is an issue in connecting, you can try starting slave with a command to skip over it:

```
SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1; 
SLAVE START; 
```