MySQL主從非同步複製 MySQL主從非同步複製 目標: 實現MySQL主從複製,達到實時備份的目的 系統概況: 系統:CentOS;內核版本:2.6.18-194.el5 MySQL: 5.1.54 網路地址: master:192.168.85.101 slave:192.168.85.102 一、安裝MySQL 二、配置 三、驗證、測試 一、安裝mysql(主從相同)01.#useradd mysql -s /sbin/nologin 02.#tar zxvf mysql-5.1.54.tar.gz 03.#cd mysql-5.1.54 04.#./configure --prefix=/usr/local/mysql --localstatedir=/opt/data --with-extra-charsets=utf8,gb2312,gbk --with-pthread --enable-thread-safe-client 05.#make 06.#make install 07.#cp support-files/my-large.cnf /etc/my.cnf 08.#cd /usr/local/mysql 09.#chgrp -R mysql . 10.#/usr/local/mysql/bin/mysql_install_db --user=mysql 11.# chown -R mysql:mysql /opt/data 12.#/usr/local/mysql/bin/mysqladmin -u root password sairl 13.#echo "/usr/local/mysql/bin/mysqld_safe --user=mysql &" >>/etc/rc.local 14.#echo "export PATH=$PATH:/usr/local/mysql/bin" >>/etc/profile 15.#source /etc/profile 複製代碼二、配置 1)、修改slave伺服器的server-id(master伺服器不變) 56 # required unique id between 1 and 2^32 - 1 57 # defaults to 1 if master-host is not set 58 # but will not function as a master if omitted 59 server-id= 10 60 61 # Replication Slave (comment out master section to use this)2)、授權(在master伺服器上操作) 01.mysql> GRANT REPLICATION SLAVE ON *.* 02.-> TO 'backup'@'192.168.85.102' IDENTIFIED BY 'testpwd'; 03.Query OK, 0 rows affected (0.00 sec) 04. 05.mysql> show master status; //主資料庫狀態 06.+------------------+----------+--------------+------------------+ 07.| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | 08.+------------------+----------+--------------+------------------+ 09.| mysql-bin.000003 | 409 | | | 10.+------------------+----------+--------------+------------------+ 複製代碼3)、配置slave伺服器01.mysql> change master to 02.-> master_host='192.168.85.101', 03.-> master_user='backup', 04.-> master_password='testpwd', 05.-> master_log_file='mysql-bin.000003', 06.-> master_log_pos=409; 07.Query OK, 0 rows affected (0.00 sec) 08.mysql> start slave; 09.Query OK, 0 rows affected (0.00 sec)複製代碼 查看一下同步狀態01.mysql> show slave status\G 02.*************************** 1. row *************************** 03. Slave_IO_State: Waiting for master to send event 04. Master_Host: 192.168.85.101 05. Master_User: replication 06. Master_Port: 3306 07. Connect_Retry: 60 08. Master_Log_File: mysql-bin.000003 09. Read_Master_Log_Pos: 720 10. Relay_Log_File: sairl-DB-2-relay-bin.000008 11. Relay_Log_Pos: 251 12. Relay_Master_Log_File: mysql-bin.000003 13. Slave_IO_Running: Yes 14. Slave_SQL_Running: Yes 15. Replicate_Do_DB: 16. Replicate_Ignore_DB: 17. Replicate_Do_Table: 18. Replicate_Ignore_Table: 19. Replicate_Wild_Do_Table: 20. Replicate_Wild_Ignore_Table: 21. Last_Errno: 0 22. Last_Error: 23. Skip_Counter: 0 24. Exec_Master_Log_Pos: 720 25. Relay_Log_Space: 556 26. Until_Condition: None 27. Until_Log_File: 28. Until_Log_Pos: 0 29. Master_SSL_Allowed: No 30. Master_SSL_CA_File: 31. Master_SSL_CA_Path: 32. Master_SSL_Cert: 33. Master_SSL_Cipher: 34. Master_SSL_Key: 35. Seconds_Behind_Master: 0 36.Master_SSL_Verify_Server_Cert: No 37. Last_IO_Errno: 0 38. Last_IO_Error: 39. Last_SQL_Errno: 0 40. Last_SQL_Error: 41.1 row in set (0.00 sec)複製代碼 PS:slave_IO進程及slave_SQL進程都必須正常運行 三、驗證、測試 主從複製測試:在master資料庫伺服器上創建庫和表,然後再插入記錄,再登陸到slave伺服器,看是否也建立相一致的庫和表以及記錄。01.mysql> create database test_db; 02.Query OK, 1 row affected (0.00 sec) 03. 04.mysql> use test_db; 05.Database changed 06.mysql> create table test_table(id int(5),name char(10)); 07.Query OK, 0 rows affected (0.00 sec) 08. 09.mysql> insert into test_table 10. -> values(01,'xiujie'); 11.Query OK, 1 row affected (0.00 sec) 12. 13.現在登錄slave資料庫伺服器,看是否成功同步。 14.mysql> show databases; 15.+--------------------+ 16.| Database | 17.+--------------------+ 18.| information_schema | 19.| mysql | 20.| test | 21.| test_db | 22.+--------------------+ 23.4 rows in set (0.02 sec) 24. 25.mysql> use test_db; 26.Database changed 27.mysql> show tables; 28.+-------------------+ 29.| Tables_in_test_db | 30.+-------------------+ 31.| test_table | 32.+-------------------+ 33.1 row in set (0.00 sec) 34. 35.mysql> select * from test_table; 36.+------+--------+ 37.| id | name | 38.+------+--------+ 39.| 1 | xiujie | 40.+------+--------+ 41.1 row in set (0.00 sec)複製代碼 OK,配置到此結束
[火星人
]
MySQL主從非同步複製 已經有651 次圍觀
本文地址: http://coctec.com/docs/service/show-post-1624.html