Centos6.5 源码搭建MYSQL5.5+MySQL主从复制

ewr23

实验环境：
        主服务器IP：192.168.1.188

        从服务器IP：192.168.1.189

一、安装MySQL由于centos6.5系统默认安装了MySQL，所以需要卸载掉自带的mysql，主，从服务器都要执行相同操作
[iyunv@localhost ~]# yum remove mysql ##卸载mysql
[iyunv@localhost ~]# cd /opt/    ##将mysql源码包下载到/opt目录下
[iyunv@localhost opt]# ls
mysql-5.5.44.tar.gz
[iyunv@localhost opt]# yum install gcc gcc-c++ ncurses-devel perl cmake  ##安装依赖包，由于MySQL5.5版本以后需要用cmake编译，所以需要安装cmake

[iyunv@localhost opt]# tar xf mysql-5.5.44.tar.gz   ##解压mysql
[iyunv@localhost opt]# cd mysql-5.5.44    ##cd到mysql解压目录
####使用cmake编译MySQL，指定安装目录为/opt/mysql,数据目录为/opt/mydata/data,配置文件目录为/opt/mysql
[iyunv@localhost mysql-5.5.44]# cmake . -DCMAKE_INSTALL_PREFIX=/opt/mysql \
         -DMYSQL_DATADIR=/opt/mydata/data \
         -DSYSCONFDIR=/opt/mysql \
                    -DWITH_INNOBASE_STORAGE_ENGINE=1 \
         -DWITH_ARCHIVE_STORAGE_ENGINE=1 \
          -DWITH_BLACKHOLE_STORAGE_ENGINE=1 \
                    -DWITH_READLINE=1 \
                    -DWITH_SSL=system \
                    -DWITH_ZLIB=system \
                    -DWITH_LIBWRAP=0 \
                    -DMYSQL_UNIX_ADDR=/tmp/mysql.sock \
                    -DDEFAULT_CHARSET=utf8 \
         -DDEFAULT_COLLATION=utf8_general_ci
[iyunv@localhost mysql-5.5.44]# make
[iyunv@localhost mysql-5.5.44]# make install
##创建程序用户，程序组。创建mysql数据目录，将/opt/mysql目录的属主和属组改为MySQL用户MySQL组
[iyunv@localhost mysql-5.5.44]# groupadd -r mysql  ##创建mysql程序组
[iyunv@localhost mysql-5.5.44]# useradd -g mysql -r -d /opt/mydata/data/ mysql ##创建mysql用户
[iyunv@localhost mysql-5.5.44]# mkdir -p /opt/mydata/data  ##创建数据目录
[iyunv@localhost mysql-5.5.44]# chown -R mysql:mysql /opt/mysql ##修改mysql安装目录的属组属主
[iyunv@localhost mysql-5.5.44]# cp support-files/my-large.cnf /opt/mysql/my.cnf ##为mysql提供配置文件
[iyunv@localhost mysql-5.5.44]# vi /opt/mysql/my.cnf ##在[mysqld]段内添加datadir= /opt/mydata/data
###为mysql提供启动脚本
[iyunv@localhost mysql-5.5.44]# cp support-files/mysql.server /etc/rc.d/init.d/mysqld  
[iyunv@localhost mysql-5.5.44]# chmod +x /etc/rc.d/init.d/mysqld ##添加执行权限
##初始化数据库，出现两个OK就初始化成功啦
[iyunv@localhost mysql-5.5.44]# cd /opt/mysql
[iyunv@localhost mysql]# ./scripts/mysql_install_db --user=mysql --datadir=/opt/mydata/data/
[iyunv@localhost mysql]# ls /opt/mydata/data/   ##初始化后就可以看到如下内容
mysql  mysql-bin.000001  mysql-bin.000002  mysql-bin.index  performance_schema  test
[iyunv@localhost mysql]# service mysqld start   ##启动mysql
Starting MySQL...                                          [确定]
[iyunv@localhost mysql]# netstat -an | grep 3306    ##查看3306端口是否开启
tcp        0      0 0.0.0.0:3306                0.0.0.0:*                   LISTEN      
[iyunv@localhost ~]# vi /etc/profile.d/mysql.sh  ##为mysql配置环境变量，添加如下内容
export PATH=/opt/mysql/bin/:PATH

[iyunv@localhost ~]# source /etc/profile.d/mysql.sh ##重新识别，至此MySQL就安装结束了
二、配置MySQL主从
1、首先配置主服务器
[iyunv@localhost ~]# service mysqld stop
[iyunv@localhost ~]# vi /opt/mysql/my.cnf ##在[mysqld]段内添加如下内容

[mysqld]

log-bin=mysql-bin       ###########启用二进制日志#############

server-id=1        ###########服务器唯一ID###########

[iyunv@localhost ~]# service mysqld start
Starting MySQL..                                           [确定]
[iyunv@localhost ~]# mysqladmin -uroot -p password "123"  #默认root用户密码为空，修改密码为123
Enter password:
[iyunv@localhost ~]# mysql -uroot -p   ##登陆mysql终端，授权给从服务器，如下面绿色内容
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.5.44-log Source distribution

Copyright (c) 2000, 2015, 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 'root'@'192.168.1.189' identified by '123';
Query OK, 0 rows affected (0.02 sec)

mysql> flush privileges;   ###刷新
Query OK, 0 rows affected (0.00 sec)

mysql> show master status;   ##查询主数据库状态
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000005 |      333 |              |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

###这里需要记录 File 以及 Position 的值，在操作从服务器时会用到，以你自身服务器的为准###

2、配置从服务器

[iyunv@localhost ~]# service mysqld stop  ##停止mysql数据库
Shutting down MySQL.                                       [确定]

[iyunv@localhost ~]# vi /opt/mysql/my.cnf ##在[mysqld]段内添加如下内容

[mysqld]

log-bin=mysql-bin       ###########启用二进制日志#############

server-id=2       ###########服务器唯一ID###########

[iyunv@localhost ~]# service mysqld start  ##启动数据库
Starting MySQL..                                           [确定]

[iyunv@localhost ~]# mysqladmin -uroot -p password "123"  #默认root用户密码为空，修改密码为123

Enter password:

[iyunv@localhost ~]# mysql -uroot -p   ###登陆mysql终端，执行如下绿色字体SQL语句
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.5.44-log Source distribution

Copyright (c) 2000, 2015, 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.

###执行同步SQL语句###

mysql> change master to
    -> master_host='192.168.1.188',
    -> master_user='root',
    -> master_password='123',
    -> master_log_file='mysql-bin.000005',
    -> master_log_pos=333;
Query OK, 0 rows affected (0.17 sec)

###启动Slave 同步进程####

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

####主从同步检查####

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State:
                  Master_Host: 192.168.1.188
                  Master_User: root
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000005
          Read_Master_Log_Pos: 333
               Relay_Log_File: localhost-relay-bin.000002
                Relay_Log_Pos: 4
        Relay_Master_Log_File: mysql-bin.000005
             Slave_IO_Running: No   ###这里应当为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: 333
              Relay_Log_Space: 107

              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: NULL
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 1593
                Last_IO_Error: Fatal error: The slave I/O thread stops because master and slave have equal MySQL server ids; these ids must be different for replication to work (or the --replicate-same-server-id option must be used on slave but this does not always make sense; please check the manual before using it).
               Last_SQL_Errno: 0
               Last_SQL_Error:
  Replicate_Ignore_Server_Ids:
             Master_Server_Id: 1
1 row in set (0.00 sec)
#####出现上方红色内容错误，由于主和从的server-id相同导致，解决办法如下########
mysql> show variables like 'server_id';   ###查看server-id
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id     | 1     |
+---------------+-------+
1 row in set (0.00 sec)

mysql> stop slave;   ###停止slave
Query OK, 0 rows affected (0.00 sec)

mysql> set global server_id=2;   ###手动修改server-id为2
Query OK, 0 rows affected (0.00 sec)

mysql> slave start;   ###再次启动slave
Query OK, 0 rows affected (0.00 sec)

mysql> show slave status\G   ####再次主从同步检查，发现两个都为yes####
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.1.188
                  Master_User: root
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000005
          Read_Master_Log_Pos: 333
               Relay_Log_File: localhost-relay-bin.000003
                Relay_Log_Pos: 253
        Relay_Master_Log_File: mysql-bin.000005
             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: 333
              Relay_Log_Space: 413

              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)

#################################到这里整个MYSQL主从的复制就完成了############################

三、测试主从服务器是否能够同步，在主服务器test数据库上创建一张名为data的表，看从服务器是否能够同步过去。

[iyunv@localhost ~]# mysql -uroot -p   ###这里为主服务器上的操作
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.5.44-log Source distribution

Copyright (c) 2000, 2015, 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> use test;  ##切换数据库到test
Database changed
##创建data表，字段name,address,phone
mysql> create table data(name varchar(20),address varchar(20),phone varchar(20));
Query OK, 0 rows affected (0.05 sec)
###插入内容，name=jerry,address=beijing,phone=13566666666
mysql> insert into data(name,address,phone) values ('jerry','beijing','13566666666');
Query OK, 1 row affected (0.03 sec)
mysql> select * from data;   ##执行select语句查询data表中的内容
+-------+---------+-------------+
| name  | address | phone       |
+-------+---------+-------------+
| jerry | beijing | 13566666666 |
+-------+---------+-------------+
1 row in set (0.00 sec)


####################切换到从服务器验证是否同步############################



[iyunv@localhost ~]# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 5.5.44-log Source distribution

Copyright (c) 2000, 2015, 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> use test; ##切换到test数据库
Database changed
mysql> show tables;  ##查看表
+----------------+
| Tables_in_test |
+----------------+
| data           |
+----------------+
1 row in set (0.00 sec)

mysql> select * from data;   ##执行select语句查看已经同步
+-------+---------+-------------+
| name  | address | phone       |
+-------+---------+-------------+
| jerry | beijing | 13566666666 |
+-------+---------+-------------+
1 row in set (0.00 sec)

mysql>

###########################至此MySQL安装+主从复制已经全部结束##############################