单台服务器(Centos)上配制主从复制（一主两从）

单机上安装一个mysql ,通过三个不同端口启动三个实例，3306，3307，3308实现一主两从架构。

一、首先安装　mysql;

安装前首先使用yum命令安装、升级所需的程序库
yum -y install gcc gcc-c++ autoconf libjpeg libjpeg-devel libpng libpng-devel freetype freetype-devel libxml2 libxml2-devel zlib zlib-devel glibc glibc-devel glib2 glib2-devel bzip2 bzip2-devel ncurses ncurses-devel curl curl-devel e2fsprogs e2fsprogs-devel krb5 krb5-devel libidn libidn-devel openssl openssl-devel openldap openldap-devel nss_ldap openldap-clients openldap-servers libxslt-devel libevent-dev ntp

------------------------------   安装 MySQL 部分    ------------------------------
创建mysql用户
groupadd mysql
useradd -g mysql mysql

安装cmake
yum install cmake

安装mysql
tar zxvf mysql-5.5.9.tar.gz
MY_dir="/home/mysqld"
cmake -DCMAKE_INSTALL_PREFIX="$MY_dir" -DDEFAULT_CHARSET=utf8  -DMYSQL_DATADIR="$MY_dir/"data/  -DCMAKE_INSTALL_PREFIX="$MY_dir" -DSYSCONFDIR="$MY_dir" -DDEFAULT_COLLATION=utf8_general_ci -DENABLE_DEBUG_SYNC=0 -DENABLED_LOCAL_INFILE=1 -DENABLED_PROFILING=1 -DWITH_READLINE=1 .
make
make install

二、完成后，复制两个mysql 数据库文件，并分别初始化三个数据库

cp mysqld/data mysqld/data_3307 -R

cp mysqld/data mysqld/data_3308 -R

/home/mysqld/scripts/mysql_install_db --user=mysql --basedir=/home/mysqld --datadir=/home/mysqld/data

/home/mysql/scripts/mysql_install_db --user=mysql --basedir=/home/mysqld --datadir=/home/mysqld/data_3307

/home/mysqld/scripts/mysql_install_db --user=mysql --basedir=/home/mysqld --datadir=/home/mysqld/data_3308

三、配制　/etc/my.cnf

[mysqld_multi]
mysqld = /home/mysqld/bin/mysqld_safe
mysqladmin = /home/mysqld/bin/mysqladmin
user = mysql
#password = mysql
[mysqld1]
server-id       = 1
port            = 3306
socket          = /tmp/mysql.sock1
pid-file        = /home/mysqld/data/db-app.pid
log             = /home/mysqld/data/db-app.log
datadir         = /home/mysqld/data
log-bin         = mysql-bin
user = mysql

[mysqld2]
server-id        = 2
port             = 3307
socket           = /tmp/mysql.sock2
pid-file         = /home/mysqld/data_3307/db-app.pid
datadir          = /home/mysqld/data_3307
log              = /home/mysqld/data_3307/db-app.log
binlog-ignore-db = mysql
binlog-ignore-db = information_schema
binlog-ignore-db = performance_schema
replicate-do-db  = test
user             = mysql

[mysqld3]
server-id        = 3
port             = 3308
socket           = /tmp/mysql.sock3
pid-file         = /home/mysqld/data_3308/db-app.pid
datadir          = /home/mysqld/data_3308
log              = /home/mysqld/data_3308/db-app.log
binlog-ignore-db = mysql
binlog-ignore-db = information_schema
binlog-ignore-db = performance_schema
replicate-do-db  = test
user             = mysql

[mysqldump]
quick
max_allowed_packet = 16M

[mysql]
no-auto-rehash

[isamchk]
key_buffer = 128M
sort_buffer_size = 128M
read_buffer = 2M
write_buffer = 2M

[myisamchk]
key_buffer = 128M
sort_buffer_size = 128M
read_buffer = 2M
write_buffer = 2M

[mysqlhotcopy]
interactive-timeout

 

/home/mysqld/bin/mysqld_multi start 1-3 //启动三个端口的mysql

/home/mysqld/bin/mysqld_multi stop 1-3 //关闭三个端口的mysql

四、进入master(3306)

/home/mysqld/bin/mysql -uroot -h127.0.0.1 -P3306

建立主从复制用户：（以下两个都要授权）

GRANT all ON *.* TO 'replication'@'127.0.0.1' IDENTIFIED BY '***';

GRANT all ON *.* TO 'replication'@'127.0.0.1' IDENTIFIED BY '***';

五、进入一个slave(3307 / 3308)

/home/mysqld/bin/mysql -uroot -h127.0.0.1 -P3307

在mysql端设置slave 对应的master信息

mysql> change master to master_host='127.0.0.1', master_port=3306, master_user='replication', master_password='***';

slave stop; //停止从复制

slave start;//启动从复制

show slave status\G;　//显示信息。

如下则成功：

mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 127.0.0.1
                  Master_User: replication
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000013
          Read_Master_Log_Pos: 107
               Relay_Log_File: db-app-relay-bin.000038
                Relay_Log_Pos: 253
        Relay_Master_Log_File: mysql-bin.000013
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: test
          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: 107
              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: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 1
1 row in set (0.00 sec)

六、进入另一个slave 重复步骤五，即可完成 单机一主两从复制。(多台也与上述类似)