mysql主從複製 (一)安裝mysql(主從伺服器皆相同) 先創建用戶 useradd mysql -s /sbin/nologin tar zxvf mysql-5.0.45.tar.gz cd mysql-5.0.45 ./configure --prefix=/usr/local/mysql --localstatedir=/opt/data --with-extra-charsets=utf8,gb2312,gbk --with-pthread --enable-thread-safe-client 注:配置過程指定數據文件的位置及另加的字符集. make make install cp support-files/my-large.cnf /etc/my.cnf cd /usr/local/mysql chgrp -R mysql . 生成基本的資料庫和表: /usr/local/mysql/bin/mysql_install_db --user=mysql 成功執行后察看數據目錄/opt/data,看是否有文件或目錄生成. chown -R mysql:mysql /opt/data 記得給每個資料庫設置root密碼. (二)修改配置文件 不同的地方就是server-id,主伺服器配置文件不用修改,從伺服器的配置文件server-id=10.其他的內容基本相同. (三)啟動服務 /usr/local/mysql/bin/mysqld_safe --user=mysql& 這個過程主輔伺服器都相同. (四)授權(在主伺服器上進行) GRANT REPLICATION SLAVE ON *.* to 'rep1'@'192.168.8.100 identified by 'mylqs'; (五)查詢主資料庫狀態(主伺服器上進行) mysql> show master status; ------------------ ---------- -------------- ------------------
| mysql> show slave statusG *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.8.101 Master_User: rep1 Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000003 Read_Master_Log_Pos: 235 Relay_Log_File: -relay-bin.000009 Relay_Log_Pos: 235 Relay_Master_Log_File: mysql-bin.000003 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 235 Relay_Log_Space: 235 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: 0 1 row in set (0.00 sec) |
| --proxy-backend-addresses=mysql_ip1:3306 --proxy-backend-addresses=mysql_ip2:3306 |
| mysql-proxy --proxy-read-only-address=<slave_ip1>:3306 --proxy-read-only-address=<slave_ip2>:3306 |
| mysql> create database first_db; Query OK, 1 row affected (0.01 sec) mysql> create table first_tb(id int(3),name char(10)); Query OK, 0 rows affected (0.00 sec) mysql> insert into first_tb values (001,'myself'); Query OK, 1 row affected (0.00 sec) 在主數據伺服器的表first_db中插入記錄 |
| mysql> show databases; -------------------- | Database | -------------------- | information_schema | | first_db | | mysql | | test | -------------------- 4 rows in set (0.01 sec) 資料庫自動生成了 mysql> use first_db; Database changed mysql> show tables; -------------------- | Tables_in_first_db | -------------------- | first_tb | -------------------- 1 row in set (0.00 sec) 表也自動生成了 mysql> select * from first_tb; ------ -------- | id | name | ------ -------- | 1 | myself | ------ -------- 1 row in set (0.00 sec) 記錄也按照我們的意願存在了 |
本文出自 「愛你linux」 博客,謝絕轉載！
[火星人 ] mysql 主從複製讀寫分離實現已經有427次圍觀
