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和相应的依赖包
[root@db1 ~]# 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
[root@db1 ~]# tar -zxvf mysql-5.1.63.tar.gz -c /usr/src/
[root@db1 ~]# useradd -m -s /sbin/nologin mysql
[root@db1 ~]# cd /usr/src/mysql-5.1.63/
[root@db1 mysql-5.1.63]# vim configure #把下面行注释掉
52297 # $rm $cfgfile
[root@db1 mysql-5.1.63]# ./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
[root@db1 mysql-5.1.63]# make
[root@db1 mysql-5.1.63]# make install
2、配置mysql
[root@db1 mysql-5.1.63]# cp support-files/my-medium.cnf /usr/local/mysql/my.cnf
[root@db1 mysql-5.1.63]# /usr/local/mysql/bin/mysql_install_db --user=mysql --basedir=/usr/local/mysql/ --datadir=/usr/local/mysql/data
[root@db1 mysql-5.1.63]# chown -r root.mysql /usr/local/mysql
[root@db1 mysql-5.1.63]# chown -r mysql /usr/local/mysql/data/
[root@db1 mysql-5.1.63]# echo /usr/local/mysql/lib/mysql >> /etc/ld.so.conf
[root@db1 mysql-5.1.63]# ldconfig
[root@db1 mysql-5.1.63]# echo export path=/usr/local/mysql/bin:$path >> /etc/profile
[root@db1 mysql-5.1.63]# source /etc/profile
[root@db1 mysql-5.1.63]# mkdir /usr/local/mysql/tmp
[root@db1 mysql-5.1.63]# chmod 777 /usr/local/mysql/tmp/
[root@db1 mysql-5.1.63]# vim /usr/local/mysql/my.cnf #修改socket位置
socket = /usr/local/mysql/tmp/mysql.sock
[root@db1 mysql-5.1.63]# mysqld_safe --defaults-file=/usr/local/mysql/my.cnf &
[root@db1 mysql-5.1.63]# 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 id is 5
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' identified by 'backuppwd';
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、打包主库迁移数据
[root@db1 mysql-5.1.63]# cd /usr/local/mysql/
[root@db1 mysql]# tar -zcf data0708.tar.gz ./data/
三、slave配置
安装过程如上，步骤省略......
[root@db2 mysql-5.1.63]# vim /usr/local/mysql/my.cnf #修改server-id
server-id = 2
将主数据库备份数据拷贝过来并解压到相应的目录
[root@db2 mysql-5.1.63]# mysqld_safe --defaults-file=/usr/local/mysql/my.cnf &
[root@db2 mysql-5.1.63]# 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 id is 2
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.