1、Mysql主从复制的优点
- 如果主服务器出现问题,可以快速的切换到从服务器提供服务
- 可以在从服务器上执行查询操作,降低主服务器的访问压力
- 可以在从服务器上执行备份,以避免备份期间影响主服务器的服务
- 注意,一般只有更新不繁琐的数据或者对实时性要求不高的数据可以通过从服务器查询,实时性要求高的数据仍然需要到主服务器数据库获取
2、My主服务器的配置
2.1 主服务的IP地址
ifconfig #查看IP地址
eth0 Link encap:Ethernet HWaddr 00:0C:29:92:E5:B7
inet addr:192.168.1.111 Bcast:192.168.1.255 Mask:255.255.255.0
[mysql]
port=3306
[client]
port=3306
socket=/tmp/mysql.sock
[mysqld]
#dir
basedir=/usr/local/mysql
datadir=/usr/local/mysql/data
tmpdir=/tmp
lc_messages_dir=/usr/local/mysql/share
slow_query_log_file=/usr/local/mysql/log/slow.log
user=mysql
log-bin=/usr/local/mysql/log/mysql-bin #一定要配置,用于开启mysql二进制日志
server_id=1 #一定要配置,在做主从复制的时候,用于区分
socket=/tmp/mysql.sock
注意:因为上面的日志文件放在了/usr/local/mysql/log目录下面，所以要在/usr/local/mysql下面，新建文件夹log.又因为，使用mysql用户启动的mysql服务器。所以，需要将新建的log文件夹的权限改为mysql:mysql用户的权限。
[root@cent01 scripts]# cd /usr/local/mysql/
[root@cent01 mysql]# mkdir log
[root@cent01 mysql]# chown -R mysql:mysql /usr/local/mysql 2.3 主服务器的mysql建立一个数据库test_bin,并备份,然后将备份文件传到从服务器
2.3.1 建立一个数据库test_bin
create database test_bin;
use test_bin;
create table table_bin
( id int
);
insert into table_bin values(1),(2),(3);
select * from table_bin;
| id |
+------+
| 1 |
| 2 |
| 3 |
+------+
2.3.2 备份数据库test_bin，并传给从服务器SCP
[root@localhost Desktop]# mysqldump -u root -p1234 test_bin -l -F > /etc/test_bin.sql
[root@localhost Desktop]# scp /etc/test_bin.sql 192.168.1.157:/etc
The authenticity of host '192.168.1.157 (192.168.1.157)' can't be established.
RSA key fingerprint is 07:07:8e:1c:c0:7e:7f:1f:ca:6a:e6:d3:cb:7f:b7:a1.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added '192.168.1.157' (RSA) to the list of known hosts.
root@192.168.1.157's password:
test_bin.sql 100% 1827 1.8KB/s 00:00
要将下面的IP地址改为从主机的IP地址。
mysql> grant all on *.* to user1@% identified by "345";
或者下面的
mysql> grant replication slave on *.* to user1@192.168.1.157 identified by "345";2.5 查看主服务器的log-bin日志
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000002 | 570 | | | |
+------------------+----------+--------------+------------------+---------------3、从服务器的配置
3.1 从服务器的IP地址
[root@localhost log]# ifconfig
eth1 Link encap:Ethernet HWaddr 00:0C:29:F0:6E:6F
inet addr:192.168.1.157 Bcast:192.168.1.255 Mask:255.255.255.0
[mysql]
port=3306
[client]
port=3306
socket=/tmp/mysql.sock
[mysqld]
#dir
basedir=/usr/local/mysql
datadir=/usr/local/mysql/data
user=mysql
tmpdir=/tmp
lc_messages_dir=/usr/local/mysql/share
slow_query_log_file=/usr/local/mysql/log/slow.log
log-bin=/usr/local/mysql/log/mysql-bin #一定要配置,用于开启mysql二进制日志
server_id=3 #一定要配置,在做主从复制的时候,用于区分
socket=/tmp/mysql.sock
注意:因为上面的日志文件放在了/usr/local/mysql/log目录下面，所以要在/usr/local/mysql下面，新建文件夹log.又因为，使用mysql用户启动的mysql服务器。所以，需要将新建的log文件夹的权限改为mysql:mysql用户的权限。
[root@cent01 scripts]# cd /usr/local/mysql/
[root@cent01 mysql]# mkdir log
[root@cent01 mysql]# chown -R mysql:mysql /usr/local/mysql 3.3 还原主服务器的mysql备份的数据库test_bin
mysql -u root -p1234 test_bin < /etc/test_bin.sql 3.4 设置主从复制的实现
注意:因为主服务器和从服务器，要进行通信。为了测试的方便，应该提前将主服务器和从服务器的防火墙关闭。防止由于防火墙的原因，IO线程连接不成功。
要将下面的IP地址改为主主机的IP地址。
mysql> stop slave;
mysql> change master to master_host="192.168.1.111",
-> master_user="user1",master_password="345",
-> master_port=3306,master_log_file="mysql-bin.000002",
-> master_log_pos=570;
mysql> start slave;
mysql>show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.1.111
Master_User: user1
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000002
Read_Master_Log_Pos: 570
Relay_Log_File: localhost-relay-bin.000002
Relay_Log_Pos: 283
Relay_Master_Log_File: mysql-bin.000002
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: 570
Relay_Log_Space: 460
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
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1
Master_UUID: 76e0dc08-fb2f-11e5-b376-000c2992e5b7
Master_Info_File: /usr/local/mysql/data/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
表示成功
4、测试主从复制
4.1主服务器的操作
mysql> insert into table_bb values(4),(5);
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> select * from table_bb;
+------+
| id |
+------+
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
+------+
5 rows in set (0.00 sec)
mysql> select * from table_bb;
+------+
| id |
+------+
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
+------+
5 rows in set (0.00 sec)
通过测试结果,发现主从复制成功.
扫一扫
352
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
