MySQL的主从同步
MySQL的主从同步是一个很成熟的架构：优点为：①在从服务器可以执行查询工作(即我们常说的读功能)，降低主服务器压力
②在从主服务器进行备份，避免备份期间影响主服务器服务
③当主服务器出现问题时，可以切换到从服务器
一、实验环境
1、IP与主机名
192.168.10.51 db1.linuxbrother.com master
192.168.10.52 db2.linuxbrother.com slave
2、所需软件
mysql-5.1.63.tar.gz
3、安装gcc和相应的依赖包
# yum -y install gcc ncurses-devel
4、数据库目录及其它
my.cnf配置文件 /usr/local/mysql/my.cnf
mysql数据库位置 /usr/local/mysql/data/
socket位置 /usr/local/mysql/tmp/mysql.sock
二、Master配置
1、安装Mysql
# tar -zxvf mysql-5.1.63.tar.gz -C /usr/src/
# useradd -M -s /sbin/nologin mysql
# cd /usr/src/mysql-5.1.63/
# vim configure #把下面行注释掉
52297 # $RM "$cfgfile"
# ./configure --prefix=/usr/local/mysql/ --enable-assembler --with-extra-charsets=complex --enable-thread-safe-client --with-big-tables --with-readline --with-ssl --with-embedded-server --enable-local-infile --with-plugins=innobase --with-mysqld-user=mysql
# make
# make install
2、配置Mysql
# cp support-files/my-medium.cnf /usr/local/mysql/my.cnf
# /usr/local/mysql/bin/mysql_install_db --user=mysql --basedir=/usr/local/mysql/ --datadir=/usr/local/mysql/data
# chown -R root.mysql /usr/local/mysql
# chown -R mysql /usr/local/mysql/data/
# echo "/usr/local/mysql/lib/mysql" >> /etc/ld.so.conf
# ldconfig
# echo "export PATH=/usr/local/mysql/bin:$PATH" >> /etc/profile
# source /etc/profile
# mkdir /usr/local/mysql/tmp
# chmod 777 /usr/local/mysql/tmp/
# vim /usr/local/mysql/my.cnf#修改socket位置
socket = /usr/local/mysql/tmp/mysql.sock
# mysqld_safe --defaults-file=/usr/local/mysql/my.cnf &
# mysql -uroot -p --socket=/usr/local/mysql/tmp/mysql.sock
Enter password:
Welcome to the MySQL monitor.Commands end with ; or \g.
Your MySQL connection> Server version: 5.1.63-log Source distribution
Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> grant replication slave on *.* to 'backup'@'192.168.10.52'> Query OK, 0 rows affected (0.00 sec)
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.000002 | 265 | | |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
3、打包主库迁移数据
# cd /usr/local/mysql/
# tar -zcf data0708.tar.gz ./data/
三、Slave配置
安装过程如上，步骤省略......
# vim /usr/local/mysql/my.cnf #修改server-id
server-id = 2
将主数据库备份数据拷贝过来并解压到相应的目录
# mysqld_safe --defaults-file=/usr/local/mysql/my.cnf &
# mysql -uroot -p --socket=/usr/local/mysql/tmp/mysql.sock
Enter password:
Welcome to the MySQL monitor.Commands end with ; or \g.
Your MySQL connection> Server version: 5.1.63-log Source distribution
Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> change master to master_host='192.168.10.51',master_user='backup',master_password='backuppwd',master_log_file='mysql-bin.000002',master_log_pos=265;
Query OK, 0 rows affected (0.24 sec)
mysql> start slave;
Query OK, 0 rows affected (0.02 sec)
mysql> show slave status\G
......
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
......
四、验证
1、在Master上面操作
# mysql -uroot -p --socket=/usr/local/mysql/tmp/mysql.sock
mysql> create database mysqltest;
Query OK, 1 row affected (0.00 sec)
mysql> use mysqltest;
Database changed
mysql> create table user (id int(5),name char(10));
Query OK, 0 rows affected (0.00 sec)
mysql> insert into user values (0001,'xieping');
Query OK, 1 row affected (0.00 sec)
mysql> insert into user values (0002,'huxinxin');
Query OK, 1 row affected (0.00 sec)
mysql> insert into user values (0003,'dingpeng');
Query OK, 1 row affected (0.00 sec)
2、在Slave上操作
# mysql -uroot -p --socket=/usr/local/mysql/tmp/mysql.sock
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| mysqltest |
| test |
+--------------------+
4 rows in set (0.02 sec)
mysql> select> +------+----------+
|> +------+----------+
| 1 | xieping|
| 2 | huxinxin |
| 3 | dingpeng |
+------+----------+
3 rows in set (0.00 sec)
页:
[1]