mysql replication 實驗

master croracle03 ip 172.16.1.168
slave croracle01 ip 172.16.1.165

1 Master Mysql server install
[root@croracle03 ~]# rpm -ivh MySQL-server-community-5.1.36-0.rhel5.x86_64.rpm
Preparing... ########################################### [100%]
1:MySQL-server-community ########################################### [100%]

090801 9:28:31 [Warning] Forcing shutdown of 2 plugins
090801 9:28:31 [Warning] Forcing shutdown of 2 plugins

PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER !
To do so, start the server, then issue the following commands:

/usr/bin/mysqladmin -u root password 'new-password'
/usr/bin/mysqladmin -u root -h croracle03.localdomain password 'new-password'

Alternatively you can run:
/usr/bin/mysql_secure_installation

which will also give you the option of removing the test
databases and anonymous user created by default. This is
strongly recommended for production servers.

See the manual for more instructions.

Please report any problems with the /usr/bin/mysqlbug script!

The latest information about MySQL is available at http://www.mysql.com/
Support MySQL by buying support/licenses from http://shop.mysql.com/

Starting MySQL....[ OK ]
Giving mysqld 2 seconds to start
[root@croracle03 ~]#
[root@croracle03 ~]#

2 Master Mysql client install
[root@croracle03 ~]# rpm -ivh MySQL-client-community-5.1.36-0.rhel5.x86_64.rpm
Preparing... ########################################### [100%]
1:MySQL-client-community ########################################### [100%]
[root@croracle03 ~]#


3 standby mysql server install

[root@croracle01 ~]# rpm -ivh MySQL-server-community-5.1.36-0.rhel5.x86_64.rpm
Preparing... ########################################### [100%]
1:MySQL-server-community ########################################### [100%]
090729 22:49:20 [Warning] Forcing shutdown of 2 plugins
090729 22:49:20 [Warning] Forcing shutdown of 2 plugins

PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER !
To do so, start the server, then issue the following commands:

/usr/bin/mysqladmin -u root password 'new-password'
/usr/bin/mysqladmin -u root -h croracle01.localdomain password 'new-password'

Alternatively you can run:
/usr/bin/mysql_secure_installation

which will also give you the option of removing the test
databases and anonymous user created by default. This is
strongly recommended for production servers.

See the manual for more instructions.

Please report any problems with the /usr/bin/mysqlbug script!

The latest information about MySQL is available at http://www.mysql.com/
Support MySQL by buying support/licenses from http://shop.mysql.com/

Starting MySQL...........[ OK ]
Giving mysqld 2 seconds to start
[root@croracle01 ~]#

4 standby mysql client install


[root@croracle01 ~]# rpm -ivh MySQL-client-community-5.1.36-0.rhel5.x86_64.rpm
Preparing... ########################################### [100%]
1:MySQL-client-community ########################################### [100%]
[root@croracle01 ~]#



5 主服務器設置replication用戶帳號
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.1.36-community MySQL Community Server (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> grant replication slave on *.* to 'repl'@'172.16.1.165' identified by 'password';
Query OK, 0 rows affected (0.00 sec)

mysql>


6 配置 my.cnf文檔
[root@croracle03 mysql]# cd /usr/share/mysql/
[root@croracle03 mysql]#

[root@croracle03 mysql]# cp my-large.cnf /etc/my.cnf

[root@croracle03 init.d]# ./mysql restart
Shutting down MySQL.. [ OK ]
Starting MySQL... [ OK ]
[root@croracle03 init.d]#



[root@croracle01 init.d]# ./mysql restart
Shutting down MySQL.. [ OK ]
Starting MySQL... [ OK ]
[root@croracle01 init.d]#


7 修改master my.conf文件
修改:
log-bin=/var/lib/mysql/mysql-bin
server-id = 1



data目錄的位置:

[root@croracle03 mysql]# pwd
/var/lib/mysql

[root@croracle03 mysql]# ls
croracle03.localdomain.err ib_logfile0 mysql-bin.000001 test
croracle03.localdomain.pid ib_logfile1 mysql-bin.index
ibdata1 mysql mysql.sock

reboot mysql
[root@croracle03 init.d]# ./mysql restart
Shutting down MySQL. [ OK ]
Starting MySQL. [ OK ]


8 master中設置讀鎖定.

mysql> flush tables with read locks;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'locks' at line 1 <--有可能會報著個錯 mysql>

--建立一個database
mysql> create database replDB
-> ;
Query OK, 1 row affected (0.00 sec)

mysql> use replDB
Database changed
mysql>
mysql> flush tables with read lock;
Query OK, 0 rows affected (0.00 sec)

mysql>


mysql> show master status;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000005 | 193 | | |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

mysql>

9 tar 和cp
[root@croracle03 mysql]# pwd
/var/lib/mysql
[root@croracle03 mysql]# ll
total 20568
-rw-rw---- 1 mysql root 6774 Aug 1 10:01 croracle03.localdomain.err
-rw-rw---- 1 mysql mysql 5 Aug 1 10:01 croracle03.localdomain.pid
-rw-rw---- 1 mysql mysql 10485760 Aug 1 10:01 ibdata1
-rw-rw---- 1 mysql mysql 5242880 Aug 1 10:01 ib_logfile0
-rw-rw---- 1 mysql mysql 5242880 Aug 1 09:28 ib_logfile1
drwx--x--x 2 mysql mysql 4096 Aug 1 09:28 mysql
-rw-rw---- 1 mysql mysql 125 Aug 1 09:55 mysql-bin.000001
-rw-rw---- 1 mysql mysql 125 Aug 1 09:57 mysql-bin.000002
-rw-rw---- 1 mysql mysql 125 Aug 1 10:01 mysql-bin.000003
-rw-rw---- 1 mysql mysql 125 Aug 1 10:01 mysql-bin.000004
-rw-rw---- 1 mysql mysql 193 Aug 1 10:06 mysql-bin.000005
-rw-rw---- 1 mysql mysql 121 Aug 1 10:01 mysql-bin.index
srwxrwxrwx 1 mysql mysql 0 Aug 1 10:01 mysql.sock
drwx------ 2 mysql mysql 4096 Aug 1 10:06 replDB
drwxr-xr-x 2 mysql mysql 4096 Aug 1 09:28 test
[root@croracle03 mysql]#



[root@croracle03 mysql]# tar cvfz replDB-master.tar.gz replDB/
replDB/
replDB/db.opt
[root@croracle03 mysql]# sftp croracle01
Connecting to croracle01...
root@croracle01's password:
sftp> cd /var/lib/mysql
sftp> put *.tar.gz
Uploading replDB-master.tar.gz to /var/lib/mysql/replDB-master.tar.gz
replDB-master.tar.gz 100% 206 0.2KB/s 00:00
sftp> quit



[root@croracle03 mysql]# mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.1.36-community-log MySQL Community Server (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> use replDB
Database changed
mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)

mysql>


--在slave上面展包
[root@croracle01 mysql]# tar xvfz replDB-master.tar.gz
replDB/
replDB/db.opt
tar: replDB/db.opt: time stamp 2009-08-01 10:06:16 is 211598 s in the future
tar: replDB: time stamp 2009-08-01 10:06:16 is 211598 s in the future
--因為兩個vmware的實例的時間不一致,不過沒有關係

在slave服務器中

/etc/my.cnf
# but will not function as a master if omitted
server-id = 2

改變啟動方式:
[root@croracle01 init.d]# ./mysql stop
Shutting down MySQL. [ OK ]
[root@croracle01 init.d]#

[root@croracle01 init.d]# which mysqld_safe
/usr/bin/mysqld_safe

[root@croracle01 bin]# chmod +x mysql_slave.sh
--自己寫了一個簡單的腳本
[root@croracle01 bin]# cat mysql_slave.sh
#!/bin/sh
/usr/bin/mysqld_safe --skip-slave-start &
[root@croracle01 bin]#


[root@croracle01 bin]# mysql_slave.sh
[root@croracle01 bin]# 090729 23:24:59 mysqld_safe Logging to '/var/lib/mysql/croracle01.localdomain.err'.
090729 23:25:00 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql

[root@croracle01 bin]#
--啟動
[root@croracle01 bin]# netstat -tln |grep 3306
tcp 0 0 0.0.0.0:3306 0.0.0.0:* LISTEN


slave的mysql配置




[root@croracle01 init.d]# mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.1.36-community-log MySQL Community Server (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> stop slave;
Query OK, 0 rows affected, 1 warning (0.00 sec)



mysql> change master to
-> master_host='172.16.1.168',
-> master_port=3306,
-> master_user='repl',
-> master_password='password',
-> master_log_file='mysql-bin.000005',
-> master_log_pos=193;
Query OK, 0 rows affected (0.07 sec)

mysql> start slave;
Query OK, 0 rows affected (0.00 sec)



mysql> show processlist \G
*************************** 1. row ***************************
Id: 1
User: root
Host: localhost
db: NULL
Command: Query
Time: 0
State: NULL
Info: show processlist
*************************** 2. row ***************************
Id: 2
User: system user
Host:
db: NULL
Command: Connect
Time: 23
State: Waiting for master to send event
Info: NULL
*************************** 3. row ***************************
Id: 3
User: system user
Host:
db: NULL
Command: Connect
Time: -211864
State: Has read all relay log; waiting for the slave I/O thread to update it
Info: NULL
3 rows in set (0.00 sec)

mysql>




開始測試:

開始測試:

Master DB

mysql> use replDB;
Database changed
mysql> create table repl_test (id int);
Query OK, 0 rows affected (0.02 sec)

mysql> insert into repl_test values(1),(2),(3),(4),(5);
Query OK, 5 rows affected (0.01 sec)
Records: 5 Duplicates: 0 Warnings: 0

mysql>

mysql> select * from repl_test;
+------+
| id |
+------+
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
+------+
5 rows in set (0.00 sec)

mysql>


slave DB



[root@croracle01 init.d]# mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.1.36-community-log MySQL Community Server (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

mysql> use replDB;
Database changed
mysql> show tables;
+------------------+
| Tables_in_replDB |
+------------------+
| repl_test |
+------------------+
1 row in set (0.00 sec)

mysql> select * from repl_test;
+------+
| id |
+------+
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
+------+
5 rows in set (0.00 sec)

mysql>