mysql主從複製

標籤：linux服務

mysql主從複製

實驗環境

主：192.168.110.33

從：192.168.110.59

iptables and selinux disabled

主：伺服器端

1.安裝並初始化

# yum install -y mysql -mysql-server

#/etc/init.d/mysql start

# mysql_secure_installation #設定root密碼

Set root password? [Y/n] y

New password:

Re-enter new password:

Password updated successfully!

Reloading privilege tables..

... Success!

Remove anonymous users? [Y/n] y

Disallow root login remotely? [Y/n] y

Remove test database and access to it? [Y/n] y

Reload privilege tables now? [Y/n] y

2.使用者、修改/etc/my.cnf檔案

# id mysql

uid=27(mysql) gid=27(mysql) groups=27(mysql)

# cp /usr/share/mysql/my-medium.cnf /etc/my.cnf

57 server-id = 1 #id唯一

59 binlog-do-db = test #指定同步test資料庫

60 binlog-ignore-db = mysql #避免同步 mysql 資料庫,以免不必要的麻煩

3.建立同步賬戶並授權

# mysql -p密碼

mysql> create database test;

Query OK, 1 row affected (0.00 sec)

mysql> use test;

mysql> create table mysql( username varchar(25) not null, password varchar(25) not null);

mysql> desc mysql;

+----------+-------------+------+-----+---------+-------+

| Field | Type | Null | Key | Default | Extra |

+----------+-------------+------+-----+---------+-------+

| username | varchar(25) | NO | | NULL | |

| password | varchar(25) | NO | | NULL | |

+----------+-------------+------+-----+---------+-------+

mysql> insert into mysql value(‘girl‘,123)；

mysql> insert into mysql value(‘boy‘,123) ;

mysql> select * from mysql;

+----------+----------+

| username | password |

+----------+----------+

| girl | 123 |

| boy | 123 |

+----------+----------+

mysql> GRANT REPLICATION SLAVE,RELOAD,SUPER ON *.* TO [email protected]‘192.168.110.%‘ IDENTIFIED BY ‘cyl&647308‘;

mysql> flush privileges;

mysql> quit

Bye

3.備份test資料庫並發送二進位日誌到從

[[email protected]_test1 mysql]# pwd

/var/lib/mysql

# file mysql-bin.000003

mysql-bin.000003: MySQL replication log

# mysqldump -p‘cyl&647308‘ test > test.sql或

#mysqldump -p‘資料庫密碼‘ test > test.sql

# scp test.sql mysql-bin.000003 192.168.110.59:

從:用戶端

1.安裝、初始化、修改/etc/my.cnf檔案

# yum install -y mysql -mysql-server

#/etc/init.d/mysql start

# mysql_secure_installation #設定root密碼

Set root password? [Y/n] y

New password:

Re-enter new password:

Password updated successfully!

Reloading privilege tables..

... Success!

Remove anonymous users? [Y/n] y

Disallow root login remotely? [Y/n] y

Remove test database and access to it? [Y/n] y

Reload privilege tables now? [Y/n] y

# vim /etc/my.cnf

server-id = 2 #id唯一

2.使用者、登入同步賬戶（使用者必須與服務端保持一致）

# id mysql

uid=27(mysql) gid=27(mysql) groups=27(mysql)

# mysql -ucyl -p‘cyl&647308‘ -h 192.168.110.33

mysql> show databases; #若沒有test資料庫手動建立

+--------------------+

| Database |

+--------------------+

| information_schema |

| mysql |

| test |

+--------------------+

mysql> quit

Bye

3.將資料庫匯入，登入資料庫看到資料同步成功（在服務端發送二進位檔案的位置）

# mysql -p‘cyl&647308‘ test<test.sql 或

#mysql -p‘資料庫密碼‘ test < test.sql

# mysql -p密碼

mysql> use test;

mysql> show tables;

+----------------+

| Tables_in_test |

+----------------+

| mysql |

+----------------+

mysql> select * from mysql;

+----------+----------+

| username | password |

+----------+----------+

| girl | 123 |

| boy | 123 |

+----------+----------+

主：服務端

在服務端查看master的狀態

mysql> show master status;

+------------------+----------+--------------+------------------+

| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |

+------------------+----------+--------------+------------------+

| mysql-bin.000003 | 1834 | test | mysql |

+------------------+----------+--------------+------------------+

從：用戶端

在用戶端設定允許服務端同步資料

# mysql -p

mysql> stop slave;

mysql> change master to master_host=‘192.168.110.33‘, master_user=‘cyl‘,master_password=‘cyl&647308‘, master_log_file=‘mysql-bin.000003‘,master_log_pos=1834; #此使用者為master端授權的同步處理的使用者

mysql> start slave;

mysql> show slave status\G;

Slave_IO_State: Waiting for master to send event

Master_Host: 192.168.110.33

Master_User: cyl

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: mysql-bin.000003

Read_Master_Log_Pos: 1834

Relay_Log_File: mysqld-relay-bin.000002

Relay_Log_Pos: 251

Relay_Master_Log_File: mysql-bin.000003

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

主：服務端

改動資料庫資料觀察從是否同步

# mysql -p密碼

mysql> use test;

mysql> insert into mysql value (‘love‘,123);

mysql> select * from mysql;

+----------+----------+

| username | password |

+----------+----------+

| girl | 123 |

| boy | 123 |

| love | 123 |

+----------+----------+

從：用戶端

# mysql -p密碼

mysql> use test;

mysql> select * from mysql;

+----------+----------+

| username | password |

+----------+----------+

| girl | 123 |

| boy | 123 |

| love | 123 |

+----------+----------+

主：服務端

mysql> delete from mysql where username=‘love‘;

從：用戶端

mysql> select * from mysql;

+----------+----------+

| username | password |

+----------+----------+

| girl | 123 |

| boy | 123 |

+----------+----------+

本文出自 “真水無香” 部落格，請務必保留此出處http://chengyanli.blog.51cto.com/11399167/1846794

mysql主從複製