MySQL物理服务器配置

转载

mob64ca1418e88d 2024-09-14 09:33:37

文章标签 MySQL物理服务器配置应用服务器 MySQL 配置管理 SQL Server 文章分类 MySQL 数据库

mysql主从(master-slave)复制安装配置
Posted by 机器人 on 6th 一月 2010 in mysql

一. 环境准备

准备两台服务器，IP分别为
192.168.1.119 (主数据库服务器）
192.168.1.120 (从数据库服务器）

分别装好mysql数据库服务器

二. 安装配置

1. 在主数据库服务器上设置一个复制使用的账户，并授予replication slave权限，这里创建一个复制用户rep1，可以从IP为192.168.1.120的主机进行连接.

mysql > grant replication slave on *.* to 'rep'@'192.168.1.120' identified by '123';

2. 修改主数据库服务器的配置文件my.cnf，开启binlog，并设置server-id的值。

[mysqld]
server-id=1
log-bin=/var/lib/mysql/mysql-bin.log

数据库重启后生效

3. 在主服务器上，设置读锁有效，这个操作是为了确保没有数据库操作，以便获得一个一致性的快照。

mysql > flush tables with read lock;

4. 然后得到主服务器上当前的二进制日志名的偏移量值。这个操作的目的是为了在从数据库启动以后，从这个点开始进行数据的恢复。

mysql> show master status; 

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

| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | 

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

| mysql-bin.000001 | 2122 | | | 

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

1 row in set (0.00 sec)

5. 建立测试数据库test1

mysql> create database test1;
Query OK, 1 row affected (0.01 sec)

6. 备份一份数据库文件，将其恢复到从服务器上，可以通过mysqldump或者直接拷贝文件的方式。这里采用直接copy文件的方式。

[~@master]# cd /var/lib/mysql
[~@master]# tar cvf test1.tar test1
test1/
test1/db.opt
[~@master]# scp test1.tar root@192.168.1.120:/var/lib/mysql/
...
[~@slave]# tar xvf test1.tar

7. 备份完毕后，主数据库恢复被写操作

mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)

8. 修改从数据库的配置文件my.cnf,添加server-id参数，注意这里的server-id参数必须唯一，不能和主数据库的配置相同.复制使用的用户、主数据库服务器的IP、端口以及连接丢失时，重试的时间

[mysqld] 

server-id=2 

master-host=192.168.1.119 

master-user=rep1 

master-password=123456 

master-connect-retry=60

9. 在从服务器上，使用–skip-slave-start 选项启动从数据库，这样就不会立即启动从数据库上的复制进程，方便对数据库的服务进程进行进一步的配置

[~@slave]# /usr/bin/mysqld_safe --skip-slave-start &
[~@slave]# Starting mysqld daemon with databases from /var/lib/mysql

10. 指定开始执行复制的日志文件和位置

mysql> change master to
-> master_log_file='mysql-bin.000001',
-> master_log_pos=2207;
Query OK, 0 rows affected (0.01 sec)

这里的log_file和log_pos是通过主服务器中执行

mysql > show master stauts;

得到

11. 在从服务器上,启动slave进程

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

12. 这时在slave上执行show processlist 命令将显示类似如下进程

mysql> show processlist \G 

*************************** 1. row *************************** 

 Id: 2 

 User: root 

 Host: localhost 

 db: NULL 

Command: Query 

 Time: 0 

 State: NULL 

 Info: show processlist 

*************************** 2. row *************************** 

 Id: 3 

 User: system user 

 Host: 

 db: NULL 

Command: Connect 

 Time: 29 

 State: Waiting for master to send event 

 Info: NULL 

*************************** 3. row *************************** 

 Id: 4 

 User: system user 

 Host: 

 db: NULL 

Command: Connect 

 Time: 29 

 State: Has read all relay log; waiting for the slave I/O thread to update it 

 Info: NULL 

3 rows in set (0.01 sec)

这表明slave已经连接上master,并开始接受并执行日志.

13.测试复制服务器的正确性,在主数据库上执行一个更新操作,观察是否在从数据库上同步.

mysql> use test1;
Database changed
mysql> create table rep(id int);
Query OK, 0 rows affected (0.00 sec)

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

15. 在从数据库上检查新表是否被创建,数据是否被同步.

mysql> use test1; 

Reading table information for completion of table and column names 

You can turn off this feature to get a quicker startup with -A 


Database changed 

mysql> show tables; 

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

| Tables_in_test1 | 

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

| rep | 

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

1 row in set (0.00 sec) 


mysql> select * from rep; 

+------+ 

| id | 

+------+ 

| 1 | 

| 2 | 

| 3 | 

| 4 | 

| 5 | 

+------+ 

5 rows in set (0.00 sec)

可以看到数据可以正确同步到从数据库上,复制服务配置成功完成.

三复制启动选项

这些选项可以在启动时加入,也可以直接写在my.cnf里

1. log-slave-updates
这个参数用来配置从服务器上的更新操作是否写进进制日志,默认不打开,但是,如果这个从服务器同时也要作为其它服务器的主服务器时,就需要启动.
这个参数需要和log-bin一起使用.

2. master-connect-retry
这个参数用来设置在和主服务器连接丢失的时候,重度的时间间隔.

3. read-only
这个参数用来设置从服务器只能接受超级用户的更新操作,从而限制应用程序错误的对从服务器的更新操作.

4. 指定复制的数据库或者表
可以使用replicate-do-db、replicate-do-table、replicate-ignore-db、replicate- ignore-table或replicate-wild-do-table来指定从主数据库复制到从从数据库的数据库或者表。

四　日常管理维护

1. 查看从服务器状态

mysql> show slave status \G; 

*************************** 1. row *************************** 

 Slave_IO_State: Waiting for master to send event 

 Master_Host: 192.168.1.119 

 Master_User: rep1 

 Master_Port: 3306 

 Connect_Retry: 60 

 Master_Log_File: mysql-bin.000001 

 Read_Master_Log_Pos: 2400 

 Relay_Log_File: mysqld-relay-bin.000002 

 Relay_Log_Pos: 428 

 Relay_Master_Log_File: mysql-bin.000001 

 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: 2400 

 Relay_Log_Space: 428 

 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) 


ERROR: 

No query specified

主要关心”Slave_IO_Running”和“Slave_SQL_Running”这两个进程的状态是否为”yes”。只要其中一个进程状态为no，则表示复制进程停止，错误原因可以从“Last_Errno”字段中的值看到。

2. 主从服务器手工同步
从服务器由于各种原因导致更新速度较慢，从而是主从服务器之间的数据差距越来越大，最终对某些应用产生影响，这种情况下，就需要定期地进行主从服务器的数据同步，使得主从服务器差距能够减到最小。常用方法是：在负载较低的时候暂时阻塞主数据库的更新，强制主从数据库更新同步。

阻塞更新

mysql> flush tables with read lock; 

Query OK, 0 rows affected (0.00 sec) 


mysql> show master status; 

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

| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | 

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

| mysql-bin.000001 | 2400 | | | 

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

1 row in set (0.00 sec)

在从服务器上，执行下面语句，其中master_pos_wait()函数的参数是前面步骤中得到的复制坐标值。

mysql> select master_pos_wait('mysql-bin.000001','2400'); 

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

| master_pos_wait('mysql-bin.000001','2400') | 

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

| 0 | 

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

1 row in set (0.00 sec)

这个语句会阻塞直到从服务器达到指定的日志文件和偏移量后，返回０，如果返回-1。

在主服务器上，执行下面语句允许主服务器重新开始处理更新

mysql> unlock tables; 

Query OK, 0 rows affected (0.00 sec) 


3. log event entry exceeded max_allowed_packet的处理 


mysql> show variables like 'max_allowed_packet'; 

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

| Variable_name | Value | 

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

| max_allowed_packet | 1048576 | 

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

1 row in set (0.01 sec) 

mysql> set @@global.max_allowed_packet=16777216

同时在my.cnf里设置max_allowed_packet=16M，保证下次数据库重新启动后参数继续有效。

好了，暂时总结这么多.

本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。