】mysqlMaster：1.1.1.22,mysqlMaster.t4x.org 2.6.32-573.22.1.el6.x86_64,CentOS release 6.7
mysqlSlave：1.1.1.101,mysqlSlave.t4x.org 3.10.0-327.13.1.el7.x86_64,CentOS Linux release 7.2.1511

[root@mysqlMaster ~]# yum install vim gcc gcc-c++ wget ncurses-devel net-tools -y
[root@mysqlMaster ~]# yum install openssl openssl-devel pcre-devel cmake libaio* library* ncurses-* -y
[root@mysqlMaster tools]# wget http://mirrors.sohu.com/mysql/MySQL-5.5/mysql-5.5.49.tar.gz
[root@mysqlMaster tools]# tar -zxf mysql-5.5.49.tar.gz
[root@mysqlMaster tools]# cd mysql-5.5.49
[root@mysqlMaster mysql-5.5.49]# cmake . -DCMAKE_INSTALL_PREFIX=/byrd/service/mysql-5.5.49 -DMYSQL_UNIX_ADDR=/byrd/service/mysql-5.5.49/tmp/mysql.sock -DMYSQL_DATADIR=/byrd/service/mysql-5.5.49/data -DWITH_DEBUG=0 -DEXTRA_CHARSETS=all -DWITH_READLINE=1 -DWITH_ZLIB=system -DWITH_SSL=system -DWITH_EMBEDDED_SERVER=1 -DENABLED_LOCAL_INFILE=1 -DMYSQL_USER=mysql -DDEFAULT_CHARSET=utf8 -DDEFAULT_COLLATION=utf8_general_ci -DWITH_INNOBASE_STORAGE_ENGINE=1 -DWITH_FEDERATED_STORAGE_ENGINE=1 -DWITH_BLACKHOLE_STORAGE_ENGINE=1 -DWITHOUT_EXAMPLE_STORAGE_ENGINE=1 -DWITH_ARCHIVE_STORAGE_ENGINE=1
[root@mysqlMaster mysql-5.5.49]# make && make install
[root@mysqlMaster mysql-5.5.49]# mkdir /data/{3306,3307}/data -p
[root@mysqlMaster mysql-5.5.49]# chown -R mysql:mysql /data/
[root@mysqlMaster mysql-5.5.49]# groupadd -r mysql -g 27 #设置mysql用户组(-r系统组也就是0-500，centos7为0-1000)
[root@mysqlMaster mysql-5.5.49]# useradd -r mysql -u 27 -g mysql -d /data/ -s /sbin/nologin #色和在系统用户，默认不会设置home目录
[root@mysqlMaster mysql-5.5.49]# ln -s /byrd/service/mysql-5.5.49 /usr/local/mysql5
[root@mysqlMaster mysql-5.5.49]# /usr/local/mysql5/scripts/mysql_install_db --basedir=/usr/local/mysql5/ --datadir=/data/3306/data --user=mysql
[root@mysqlMaster mysql-5.5.49]# /usr/local/mysql5/scripts/mysql_install_db --basedir=/usr/local/mysql5/ --datadir=/data/3307/data --user=mysql
[root@mysqlSlave /]# /usr/local/mysql5/scripts/mysql_install_db --basedir=/usr/local/mysql5/ --datadir=/data/3308/data --user=mysql
[root@mysqlSlave /]# /usr/local/mysql5/scripts/mysql_install_db --basedir=/usr/local/mysql5/ --datadir=/data/3309/data --user=mysql
[root@mysqlMaster 3306]# cat /data/3306/my.cnf | grep -v ^
[client]
port = 3306
socket = /data/3306/mysql.sock
[mysql]
no-auto-rehash
[mysqld]
user = mysql
port = 3306
socket = /data/3306/mysql.sock
basedir = /usr/local/mysql5
datadir = /data/3306/data
[root@mysqlMaster 3306]# /usr/local/mysql5/bin/mysqld_safe --defaults-file=/data/3306/my.cnf &
[root@mysqlMaster 3306]# /usr/local/mysql5/bin/mysqld_safe --defaults-file=/data/3307/my.cnf &
[root@mysqlMaster 3306]# netstat -tunlp|grep mysql
tcp 0 0 0.0.0.0:3306 0.0.0.0:* LISTEN 13416/mysqld
tcp 0 0 0.0.0.0:3307 0.0.0.0:* LISTEN 13265/mysqld
[root@mysqlMaster 3306]# /usr/local/mysql5/bin/mysql -uroot -p -S /data/3306/mysql.sock #首次空密码
[root@mysqlMaster 3306]# /usr/local/mysql5/bin/mysql -uroot -p'admin123' -S /data/3306/mysql.sock
[root@mysqlMaster 3306]# /usr/local/mysql5/bin/mysql -uroot -p'admin456' -S /data/3307/mysql.sock
[root@mysqlMaster /]# /usr/local/mysql5/bin/mysqld_safe --defaults-file=/data/3306/my.cnf &
[root@mysqlMaster /]# /usr/local/mysql5/bin/mysqld_safe --defaults-file=/data/3307/my.cnf &
mysql> update mysql.user set password=PASSWORD('admin123') where user='root';
mysql> flush privileges;
# yum install vim gcc gcc-c++ wget ncurses-devel net-tools -y
root
mysqlMaster
# yum install openssl openssl-devel pcre-devel cmake libaio* library* ncurses-* -y
root
mysqlMaster
tools
# wget http://mirrors.sohu.com/mysql/MySQL-5.5/mysql-5.5.49.tar.gz
root
mysqlMaster
tools
# tar -zxf mysql-5.5.49.tar.gz
root
mysqlMaster
tools
# cd mysql-5.5.49
root
mysqlMaster
mysql
5.5.49
# cmake . -DCMAKE_INSTALL_PREFIX=/byrd/service/mysql-5.5.49 -DMYSQL_UNIX_ADDR=/byrd/service/mysql-5.5.49/tmp/mysql.sock -DMYSQL_DATADIR=/byrd/service/mysql-5.5.49/data -DWITH_DEBUG=0 -DEXTRA_CHARSETS=all -DWITH_READLINE=1 -DWITH_ZLIB=system -DWITH_SSL=system -DWITH_EMBEDDED_SERVER=1 -DENABLED_LOCAL_INFILE=1 -DMYSQL_USER=mysql -DDEFAULT_CHARSET=utf8 -DDEFAULT_COLLATION=utf8_general_ci -DWITH_INNOBASE_STORAGE_ENGINE=1 -DWITH_FEDERATED_STORAGE_ENGINE=1 -DWITH_BLACKHOLE_STORAGE_ENGINE=1 -DWITHOUT_EXAMPLE_STORAGE_ENGINE=1 -DWITH_ARCHIVE_STORAGE_ENGINE=1
root
mysqlMaster
mysql
5.5.49
# make && make install
root
mysqlMaster
mysql
5.5.49
# mkdir /data/{3306,3307}/data -p
root
mysqlMaster
mysql
5.5.49
# chown -R mysql:mysql /data/
root
mysqlMaster
mysql
5.5.49
# groupadd -r mysql -g 27    #设置mysql用户组(-r系统组也就是0-500，centos7为0-1000)
root
mysqlMaster
mysql
5.5.49
# useradd -r mysql -u 27 -g mysql -d /data/ -s /sbin/nologin    #色和在系统用户，默认不会设置home目录
root
mysqlMaster
mysql
5.5.49
# ln -s /byrd/service/mysql-5.5.49 /usr/local/mysql5
root
mysqlMaster
mysql
5.5.49
# /usr/local/mysql5/scripts/mysql_install_db --basedir=/usr/local/mysql5/ --datadir=/data/3306/data --user=mysql
root
mysqlMaster
mysql
5.5.49
# /usr/local/mysql5/scripts/mysql_install_db --basedir=/usr/local/mysql5/ --datadir=/data/3307/data --user=mysql
root
mysqlSlave
# /usr/local/mysql5/scripts/mysql_install_db --basedir=/usr/local/mysql5/ --datadir=/data/3308/data --user=mysql
root
mysqlSlave
# /usr/local/mysql5/scripts/mysql_install_db --basedir=/usr/local/mysql5/ --datadir=/data/3309/data --user=mysql
root
mysqlMaster
3306
# cat /data/3306/my.cnf | grep -v ^
client
port
3306
socket
data
3306
mysql
sock
mysql
auto
rehash
mysqld
user
mysql
port
3306
socket
data
3306
mysql
sock
basedir
local
mysql5
datadir
data
3306
data
root
mysqlMaster
3306
# /usr/local/mysql5/bin/mysqld_safe --defaults-file=/data/3306/my.cnf &
root
mysqlMaster
3306
# /usr/local/mysql5/bin/mysqld_safe --defaults-file=/data/3307/my.cnf &
root
mysqlMaster
3306
# netstat -tunlp|grep mysql
0.0.0.0
3306
0.0.0.0
LISTEN
13416
mysqld
0.0.0.0
3307
0.0.0.0
LISTEN
13265
mysqld
root
mysqlMaster
3306
# /usr/local/mysql5/bin/mysql -uroot -p -S /data/3306/mysql.sock   #首次空密码
root
mysqlMaster
3306
# /usr/local/mysql5/bin/mysql -uroot -p'admin123' -S /data/3306/mysql.sock
root
mysqlMaster
3306
# /usr/local/mysql5/bin/mysql -uroot -p'admin456' -S /data/3307/mysql.sock
root
mysqlMaster
# /usr/local/mysql5/bin/mysqld_safe --defaults-file=/data/3306/my.cnf &
root
mysqlMaster
# /usr/local/mysql5/bin/mysqld_safe --defaults-file=/data/3307/my.cnf &
mysql
>
update
mysql
user
set
password
PASSWORD
'admin123'
where
user
'root'
mysql
>
flush
privileges
[root@mysqlMaster /]# /usr/local/mysql5/bin/mysql -uroot -p'admin123' -S /data/3306/mysql.sock
mysql> select user,host from mysql.user;
+------+--------------------+
| user | host |
+------+--------------------+
| root | 127.0.0.1 |
| root | ::1 |
| | localhost |
| root | localhost |
| | mysqlslave.t4x.org |
| root | mysqlslave.t4x.org |
+------+--------------------+
mysql> delete from mysql.user;
Query OK, 6 rows affected (0.00 sec)
mysql> select * from mysql.user\G
mysql> grant all privileges on *.* to system@'localhost' identified by 'admin123' with grant option;
mysql> flush privileges;
mysql> select * from mysql.user\G
*************************** 1. row ***************************
Host: localhost
User: system
Password: *01A6717B58FF5C7EAFFF6CB7C96F7428EA65FE4C
mysql> create database da3306t21 default character set utf8 collate utf8_general_ci;
mysql> drop database test;
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| da3306t21 |
| mysql |
| performance_schema |
+--------------------+
mysql> grant replication slave on *.* to byrd@'1.1.1.101' identified by 'passwd123'; #授权从库ip
mysql> select user,host from mysql.user;
+--------+-----------+
| user | host |
+--------+-----------+
| byrd | 1.1.1.101 |
| system | localhost |
+--------+-----------+
mysql> flush tables with read lock; #锁表
mysql> show variables like '%timeout%'; #查询超时时间，默认28800
mysql> show variables like '%timeout%';
+----------------------------+----------+
| Variable_name | Value |
+----------------------------+----------+
| interactive_timeout | 28800 |
| wait_timeout | 28800 |
+----------------------------+----------+
mysql> show master status; #获取bin_log位置点
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 | 188 | | |
+------------------+----------+--------------+------------------+
[root@mysqlMaster /]# /usr/local/mysql5/bin/mysqldump -usystem -p'admin123' -S /data/3306/mysql.sock --events -B -A | gzip > /tmp/all.sql.gz
mysql> show master status; #查询位置是否偏移（-F 除外）
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 | 188 | | |
+------------------+----------+--------------+------------------+
[root@mysqlMaster /]# scp -P22 /tmp/all.sql.gz root@1.1.1.101:/tmp
root@1.1.1.101's password:
all.sql.gz 100% 148KB 148.2KB/s 00:00
[root@mysqlMaster /]# egrep "server-id|log" /data/3306/my.cnf
log-bin = /data/3306/data/mysql-bin
server-id = 1
[root@mysqlMaster
# /usr/local/mysql5/bin/mysql -uroot -p'admin123' -S /data/3306/mysql.sock
mysql
>
select
user,
host
from
mysql.
user;
+------+--------------------+
user
host
|
+------+--------------------+
root
127.0.0.1
|
root
|
|
localhost
|
root
localhost
|
|
mysqlslave.t4x.org
root
mysqlslave.t4x.org
+------+--------------------+
mysql
>
delete
from
mysql.
user;
Query
rows
affected
(0.00
sec)
mysql
>
select
from
mysql.
user\G
mysql
>
grant
privileges
system@
'localhost'
identified by
'admin123'
with grant option;
mysql
>
flush
privileges;
mysql
>
select
from
mysql.
user\G
***************************
***************************
Host:
localhost
User:
system
Password:
*01A6717B58FF5C7EAFFF6CB7C96F7428EA65FE4C
mysql
>
create
database
da3306t21
default
character set
utf8
collate
utf8_general_ci;
mysql
>
drop
database
test;
mysql
>
show
databases;
+--------------------+
Database
|
+--------------------+
information_schema
da3306t21
|
mysql
|
performance_schema
+--------------------+
mysql
>
grant
replication
slave
byrd@
'1.1.1.101'
identified by
'passwd123';
#授权从库ip      
mysql
>
select
user,
host
from
mysql.
user;
+--------+-----------+
user
|
host
|
+--------+-----------+
byrd
|
1.1.1.101
system
localhost
+--------+-----------+
mysql
>
flush
tables
with
read
lock;
mysql
>
show
variables
like
'%timeout%';
#查询超时时间，默认28800
mysql
>
show
variables
like
'%timeout%';
+----------------------------+----------+
Variable_name
|
Value
|
+----------------------------+----------+
interactive_timeout
|
28800
|
wait_timeout
|
28800
|
+----------------------------+----------+
mysql
>
show
master status;
#获取bin_log位置点
+------------------+----------+--------------+------------------+
File
|
Position
Binlog_Do_DB
Binlog_Ignore_DB
+------------------+----------+--------------+------------------+
mysql
bin.000001
188
|
|
+------------------+----------+--------------+------------------+
[root@mysqlMaster
# /usr/local/mysql5/bin/mysqldump -usystem -p'admin123' -S /data/3306/mysql.sock --events -B -A | gzip > /tmp/all.sql.gz
mysql
>
show
master status;
#查询位置是否偏移（-F 除外）
+------------------+----------+--------------+------------------+
File
|
Position
Binlog_Do_DB
Binlog_Ignore_DB
+------------------+----------+--------------+------------------+
mysql
bin.000001
188
|
|
+------------------+----------+--------------+------------------+
[root@mysqlMaster
# scp -P22 /tmp/all.sql.gz root@1.1.1.101:/tmp
root@1.1.1.101's
password:
all.
sql.gz
100%
148KB
148.2KB
00:00
[root@mysqlMaster
# egrep "server-id|log" /data/3306/my.cnf
/data
/3306
/data
/mysql
server
[root@mysqlSlave /]# egrep "server-id|log" /data/3308/my.cnf #需要重新启动mysql
#log-bin = /data/3308/data/mysql-bin #备份服务器、下面有其他从库【级联同步】，需要开bin_log
server-id = 999
[root@mysqlSlave /]# ll /tmp/
-rw-r--r--. 1 root root 151725 Apr 22 01:34 all.sql.gz
[root@mysqlSlave /]# gzip -d /tmp/all.sql.gz
[root@mysqlSlave /]# ll /tmp/
-rw-r--r--. 1 root root 552100 Apr 22 01:34 all.sql
[root@mysqlSlave /]# /usr/local/mysql5/bin/mysql -usystem -p'admin123' -S /data/3308/mysql.sock
ERROR 1045 (28000): Access denied for user 'system'@'localhost' (using password: YES)
[root@mysqlSlave /]# /usr/local/mysql5/bin/mysql -uroot -p'' -S /data/3308/mysql.sock
mysql> select user,host from mysql.user;
+--------+-----------+
| user | host |
+--------+-----------+
| byrd | 1.1.1.101 |
| system | localhost |
+--------+-----------+
mysql> flush privileges;
[root@mysqlSlave /]# /usr/local/mysql5/bin/mysql -usystem -p'admin123' -S /data/3308/mysql.sock
mysql> CHANGE MASTER TO
-> MASTER_HOST='1.1.1.22',
-> MASTER_PORT=3306,
-> MASTER_USER='byrd',
-> MASTER_PASSWORD='passwd123',
-> MASTER_LOG_FILE='mysql-bin.000001',
-> MASTER_LOG_POS=188;
Query OK, 0 rows affected (0.25 sec)
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 1.1.1.22
Master_User: byrd
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 188
Relay_Log_File: mysqlSlave-relay-bin.000002
Relay_Log_Pos: 253
Relay_Master_Log_File: mysql-bin.000004
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Seconds_Behind_Master: 0
[root@mysqlSlave
# egrep "server-id|log" /data/3308/my.cnf  #需要重新启动mysql
#log-bin = /data/3308/data/mysql-bin     #备份服务器、下面有其他从库【级联同步】，需要开bin_log
server
[root@mysqlSlave
# ll /tmp/
-r--r--.
root
root
151725
01:34
all.
sql.gz
[root@mysqlSlave
# gzip -d /tmp/all.sql.gz
[root@mysqlSlave
# ll /tmp/
-r--r--.
root
root
552100
01:34
all.
[root@mysqlSlave
# /usr/local/mysql5/bin/mysql -usystem -p'admin123' -S /data/3308/mysql.sock
ERROR
1045
(28000):
Access
denied
user
'system'@
'localhost'
using
password:
YES)
[root@mysqlSlave
# /usr/local/mysql5/bin/mysql -uroot -p'' -S /data/3308/mysql.sock
mysql
>
select
user,
host
from
mysql.
user;
+--------+-----------+
user
|
host
|
+--------+-----------+
byrd
|
1.1.1.101
system
localhost
+--------+-----------+
mysql
>
flush
privileges;
[root@mysqlSlave
# /usr/local/mysql5/bin/mysql -usystem -p'admin123' -S /data/3308/mysql.sock
mysql
>
CHANGE
MASTER
-
>
MASTER_HOST=
'1.1.1.22',
-
>
MASTER_PORT
=3306,
-
>
MASTER_USER=
'byrd',
-
>
MASTER_PASSWORD=
'passwd123',
-
>
MASTER_LOG_FILE=
'mysql-bin.000001',
-
>
MASTER_LOG_POS
=188;
Query
rows
affected
(0.25
sec)
mysql
>
show
slave status\G
***************************
***************************
Slave_IO_State:
Waiting
master
send
event
Master_Host:
1.1.1.22
Master_User:
byrd
Master_Port:
3306
Connect_Retry:
Master_Log_File:
mysql
bin.000001
Read_Master_Log_Pos:
Relay_Log_File:
mysqlSlave
-relay
bin.000002
Relay_Log_Pos:
Relay_Master_Log_File:
mysql
bin.000004
Slave_IO_Running:
Slave_SQL_Running:
Seconds_Behind_Master: