- 论坛徽章:
- 1
|
目标: 实现MySQL主从复制,达到实时备份的目的
系统概况: 系统:CentOS;内核版本:2.6.18-194.el5 MySQL: 5.1.54
网络地址: master:192.168.85.101 slave:192.168.85.102
一、安装MySQL
二、配置
三、验证、测试
一、安装mysql(主从相同)- #useradd mysql -s /sbin/nologin
- #tar zxvf mysql-5.1.54.tar.gz
- #cd mysql-5.1.54
- #./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
- # chown -R mysql:mysql /opt/data
- # /usr/local/mysql/bin/mysqld_safe --user=mysql &
- #/usr/local/mysql/bin/mysqladmin -u root password sairl
- #echo "/usr/local/mysql/bin/mysqld_safe --user=mysql &" >>/etc/rc.local
- #echo "export PATH=$PATH:/usr/local/mysql/bin" >>/etc/profile
- #source /etc/profile
复制代码 二、配置
1)、修改slav服务器的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服务器上操作)- mysql> GRANT REPLICATION SLAVE ON *.*
- -> TO 'backup'@'192.168.85.102' IDENTIFIED BY 'testpwd';
- Query OK, 0 rows affected (0.00 sec)
- mysql> show master status; //主数据库状态
- +------------------+----------+--------------+------------------+
- | File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
- +------------------+----------+--------------+------------------+
- | mysql-bin.000003 | 409 | | |
- +------------------+----------+--------------+------------------+
复制代码 3)、配置slave服务器- mysql> change master to
- -> master_host='192.168.85.101',
- -> master_user='backup',
- -> master_password='testpwd',
- -> master_log_file='mysql-bin.000003',
- -> master_log_pos=409;
- Query OK, 0 rows affected (0.00 sec)
- mysql> start slave;
- Query OK, 0 rows affected (0.00 sec)
复制代码 查看一下同步状态- mysql> show slave status\G
- *************************** 1. row ***************************
- Slave_IO_State: Waiting for master to send event
- Master_Host: 192.168.85.101
- Master_User: replication
- Master_Port: 3306
- Connect_Retry: 60
- Master_Log_File: mysql-bin.000003
- Read_Master_Log_Pos: 720
- Relay_Log_File: sairl-DB-2-relay-bin.000008
- Relay_Log_Pos: 251
- 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: 720
- Relay_Log_Space: 556
- 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:
- 1 row in set (0.00 sec)
复制代码 PS:slave_IO进程及slave_SQL进程都必须正常运行
三、验证、测试
主从复制测试:在master数据库服务器上创建库和表,然后再插入记录,再登陆到slave服务器,看是否也建立相一致的库和表以及记录。- mysql> create database test_db;
- Query OK, 1 row affected (0.00 sec)
- mysql> use test_db;
- Database changed
- mysql> create table test_table(id int(5),name char(10));
- Query OK, 0 rows affected (0.00 sec)
- mysql> insert into test_table
- -> values(01,'xiujie');
- Query OK, 1 row affected (0.00 sec)
- 现在登录slave数据库服务器,看是否成功同步。
- mysql> show databases;
- +--------------------+
- | Database |
- +--------------------+
- | information_schema |
- | mysql |
- | test |
- | test_db |
- +--------------------+
- 4 rows in set (0.02 sec)
- mysql> use test_db;
- Database changed
- mysql> show tables;
- +-------------------+
- | Tables_in_test_db |
- +-------------------+
- | test_table |
- +-------------------+
- 1 row in set (0.00 sec)
- mysql> select * from test_table;
- +------+--------+
- | id | name |
- +------+--------+
- | 1 | xiujie |
- +------+--------+
- 1 row in set (0.00 sec)
复制代码 OK,配置到此结束 |
|