MySQL5.6主从复制方案

1.主备服务器操作

环境：CentOS 6.3/6.4 最小化缺省安装，配置好网卡。

安装MySQL前，确认Internet连接正常，以便下载安装文件。

＃新增用户组
groupadd mysql

＃
新增用户
useradd mysql -g mysql

＃
新建数据库执行文件目录
mkdir -p /usr/local/mysql

＃
新建数据库数据文件目录
mkdir -p /db/mysql/data

＃
编辑PATH搜索路径
vi /etc/profile
PATH=/usr/local/mysql/bin:/usr/local/mysql/lib:$PATH
export PATH

＃
生效PATH搜索路径
source /etc/profile

＃
安装编译源码所需的工具和库
yum -y install wget gcc-c++ ncurses-devel cmake make perl

＃
进入源码压缩包下载目录
cd /usr/local/src

＃
下载源码压缩包
wget
http://dev.mysql.com/get/Downloads/MySQL-5.6/mysql-5.6.10.tar.gz/from/http://cdn.mysql.com/

＃
解压缩源码包
tar -zxvf mysql-5.6.10.tar.gz

＃
进入解压缩源码目录
cd mysql-5.6.10

＃从mysql5.5起，mysql源码安装开始使用cmake了，执行源码编译配置脚本。

cmake \
-DCMAKE_INSTALL_PREFIX=/usr/local/mysql \
-DMYSQL_UNIX_ADDR=/usr/local/mysql/mysql.sock \
-DDEFAULT_CHARSET=utf8 \
-DDEFAULT_COLLATION=utf8_general_ci \
-DWITH_MYISAM_STORAGE_ENGINE=1 \
-DWITH_INNOBASE_STORAGE_ENGINE=1 \
-DWITH_ARCHIVE_STORAGE_ENGINE=1 \
-DWITH_BLACKHOLE_STORAGE_ENGINE=1 \
-DWITH_MEMORY_STORAGE_ENGINE=1 \
-DWITH_READLINE=1 \
-DENABLED_LOCAL_INFILE=1 \
-DMYSQL_DATADIR=/db/mysql/data \
-DMYSQL_USER=mysql \
-DMYSQL_TCP_PORT=3306

＃
编译源码，这一步时间会较长，耐心等待。
make

＃
安装
make install

＃
清除安装临时文件
make clean

＃
修改目录拥有者
chown -R mysql:mysql /usr/local/mysql
chown -R mysql:mysql /db/mysql/data

＃
进入安装路径
cd /usr/local/mysql

＃
执行初始化配置脚本，创建系统自带的数据库和表。
scripts/mysql_install_db --user=mysql --datadir=/db/mysql/data
初始化脚本在
/usr/local/mysql/my.cnf 生成了配置文件。需要更改该配置文件的所有者：
chown -R mysql:mysql /usr/local/mysql
mv /etc/my.cnf /etc/my.cnf.bak

＃
复制服务启动脚本
cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysql

＃
启动MySQL服务
service mysql start

# 设置开机自动启动服务
chkconfig mysql on

# 修改MySQL用户root的密码
mysql -u root

mysql>use mysql;
mysql>GRANT ALL PRIVILEGES ON *.* TO root@"%" IDENTIFIED BY
"root";
mysql>update user set Password = password('123456') where User='root';
mysql>flush privileges;
mysql>exit;

移除test数据库和匿名用户，强烈建议生产服务器使用：

/usr/local/mysql/bin/mysql_secure_installation

2.修改主服务器master:

vi /usr/local/mysql/my.cnf

[mysqld]

log-bin=mysql-bin

server-id=1

3.修改从服务器slave:

vi /usr/local/mysql/my.cnf

[mysqld]

log-bin=mysql-bin

server-id=2

4.重启两台服务器的MySQL

service mysql restart

5.在主服务器上建立账户并授权slave:

[root@cd2 mysql]# mysql -u root –p

mysql> grant replication slave on *.* to repl@'%' identified by
'replica';

Query OK, 0 rows affected (0.00 sec)

6.登录主服务器的MySQL，查询master的状态并记录master的复制坐标(从库用改点的坐标开始从主服务器上进行更新)

mysql> show master status;

+------------------+----------+--------------+------------------+-------------------+

+------------------+----------+--------------+------------------+-------------------+

| mysql-bin.000001 | 318
| | | |

+------------------+----------+--------------+------------------+-------------------+

1 row in set (0.00 sec)

7.配置从服务器slave

[root@cd3 mysql]# mysql -u root –p

mysql> change master to

->
master_host='192.168.100.185',

-> master_user='repl',

->
master_password='replica',

->
master_log_file='mysql-bin.000001',

-> master_log_pos=318;

Query OK, 0 rows affected, 2 warnings (0.27 sec)

mysql> start slave;

Query OK, 0 rows affected (0.03 sec)

8.检查从服务器复制功能状态：

mysql> show slave status\G

*************************** 1. row ***************************

Slave_IO_State:
Waiting for master to send event

Master_Host:
192.168.100.185

Master_User:
repl

Master_Port: 3306

Connect_Retry: 60

Master_Log_File:
mysql-bin.000001

Read_Master_Log_Pos:
318

Relay_Log_File:
cd3-relay-bin.000002

Relay_Log_Pos:
283

Relay_Master_Log_File:
mysql-bin.000001

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:
318

Relay_Log_Space:
454

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

Master_UUID:
583d568a-094e-11e5-8a35-00163eef6712

Master_Info_File:
/db/mysql/data/master.info

SQL_Delay: 0

SQL_Remaining_Delay:
NULL

Slave_SQL_Running_State:
Slave has read all relay log; waiting for the slave I/O thread to update it

Master_Retry_Count:
86400

Master_Bind:

Last_IO_Error_Timestamp:

Last_SQL_Error_Timestamp:

Master_SSL_Crl:

Master_SSL_Crlpath:

Retrieved_Gtid_Set:

Executed_Gtid_Set:

Auto_Position: 0

1 row in set (0.00 sec)

其中Slave_IO和Slave_SQL进程必须正常，即状态为Yes，否则都是错误的状态(有一个为NO就为错误)

9.主从服务器测试

在主库中创建数据库

mysql> create database student_db;

Query OK, 1 row affected (0.00 sec)

在mysql中插入数据

mysql> use student_db;

Database changed

mysql> create table student_tb(std_id char(20),std_name
char(20),std_sex char(1),std_class,char(1));

mysql> insert into student_tb values('0000001','li hua','M','1');

Query OK, 1 row affected (0.03 sec)

mysql> insert into student_tb values('0000002','li shan','F','2');

Query OK, 1 row affected (0.02 sec)

mysql> insert into student_tb values('0000003','song qi','F','3');

Query OK, 1 row affected (0.02 sec)

mysql> select * from student_db.student_tb;

+---------+----------+---------+-----------+

+---------+----------+---------+-----------+

| 0000001 | li hua | M |
1 |

| 0000002 | li shan | F | 2 |

| 0000003 | song qi | F | 3 |

+---------+----------+---------+-----------+

3 rows in set (0.00 sec)

mysql> show master status;

+------------------+----------+--------------+------------------+-------------------+

+------------------+----------+--------------+------------------+-------------------+

| mysql-bin.000001 | 1405
| | | |

+------------------+----------+--------------+------------------+-------------------+

1 row in set (0.00 sec)

从服务器MySQL查询

mysql> show databases;

+--------------------+

| Database |

+--------------------+

| information_schema |

| mysql |

| performance_schema |

| student_db |

| test |

+--------------------+

5 rows in set (0.00 sec)

mysql> use student_db;

Reading table information for completion of table and column names

You can turn off this feature to get a quicker startup with -A

Database changed

mysql> show tables;

+----------------------+

| Tables_in_student_db |

+----------------------+

| student_tb |

+----------------------+

1 row in set (0.00 sec)

mysql> select * from student_tb;

+---------+----------+---------+-----------+

+---------+----------+---------+-----------+

| 0000001 | li hua | M | 1 |

| 0000002 | li shan | F |
2 |

| 0000003 | song qi | F | 3 |

+---------+----------+---------+-----------+

3 rows in set (0.00 sec)

mysql> show slave status\G

*************************** 1. row ***************************

Slave_IO_State:
Waiting for master to send event

Master_Host:
192.168.100.185

Master_User:
repl

Master_Port:
3306

Connect_Retry: 60

Master_Log_File:
mysql-bin.000001

Read_Master_Log_Pos:
1405

Relay_Log_File:
cd3-relay-bin.000002

Relay_Log_Pos:
1370

Relay_Master_Log_File:
mysql-bin.000001

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:
1405

Relay_Log_Space:
1541

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

Master_UUID:
583d568a-094e-11e5-8a35-00163eef6712

Master_Info_File:
/db/mysql/data/master.info

SQL_Delay: 0

SQL_Remaining_Delay:
NULL

Slave_SQL_Running_State:
Slave has read all relay log; waiting for the slave I/O thread to update it

Master_Retry_Count:
86400

Master_Bind:

Last_IO_Error_Timestamp:

Last_SQL_Error_Timestamp:

Master_SSL_Crl:

Master_SSL_Crlpath:

Retrieved_Gtid_Set:

Executed_Gtid_Set:

Auto_Position: 0

1 row in set (0.00 sec)

MySQL RPM安装
1检查MySQL及相关RPM包，是否安装，如果有安装，则移除（rpm –e 名称）
[root@test01 ~]# rpm -qa|grep -i mysql
mysql-libs-5.1.71-1.el6.x86_64
rpm --nodeps -e mysql-libs
[root@test01 ~]# yum -y remove mysql-libs*(or rpm --nodeps -e mysql-libs)
2下载MySQL
[root@test01 opt]# ll
total 115244
-rw-r--r--. 1 root root 23624679 Jun 27 21:25 MySQL-client-5.6.31-1.linux_glibc2.5.x86_64.rpm
-rw-r--r--. 1 root root 4588279 Jun 27 21:16 MySQL-devel-5.6.31-1.linux_glibc2.5.x86_64.rpm
-rw-r--r--. 1 root root 89759062 Jun 27 21:36 MySQL-server-5.6.31-1.linux_glibc2.5.x86_64.rpm
3安装MySQL
[root@test01 opt]# rpm -ivh MySQL-server-5.6.31-1.linux_glibc2.5.x86_64.rpm
warning: MySQL-server-5.6.31-1.linux_glibc2.5.x86_64.rpm: Header V3 DSA/SHA1 Signature, key ID 5072e1f5: NOKEY
Preparing... ########################################### [100%]
1:MySQL-server ########################################### [100%]
warning: user mysql does not exist - using root
warning: group mysql does not exist - using root
A RANDOM PASSWORD HAS BEEN SET FOR THE MySQL root USER !
You will find that password in '/root/.mysql_secret'.

You must change that password on your first connect,
no other statement but 'SET PASSWORD' will be accepted.
See the manual for the semantics of the 'password expired' flag.

Also, the account for the anonymous user has been removed.

In addition, you can run:

/usr/bin/mysql_secure_installation

which will also give you the option of removing the test database.
This is strongly recommended for production servers.

See the manual for more instructions.

Please report any problems at http://bugs.mysql.com/

The latest information about MySQL is available on the web at

http://www.mysql.com

Support MySQL by buying support/licenses at http://shop.mysql.com

New default config file was created as /usr/my.cnf and
will be used by default by the server when you start it.
You may edit this file to change server settings
[root@test01 opt]# rpm -ivh MySQL-devel-5.6.31-1.linux_glibc2.5.x86_64.rpm
warning: MySQL-devel-5.6.31-1.linux_glibc2.5.x86_64.rpm: Header V3 DSA/SHA1 Signature, key ID 5072e1f5: NOKEY
Preparing... ########################################### [100%]
1:MySQL-devel ########################################### [100%]
[root@test01 opt]# rpm -ivh MySQL-client-5.6.31-1.linux_glibc2.5.x86_64.rpm
warning: MySQL-client-5.6.31-1.linux_glibc2.5.x86_64.rpm: Header V3 DSA/SHA1 Signature, key ID 5072e1f5: NOKEY
Preparing... ########################################### [100%]
1:MySQL-client ########################################### [100%]
修改配置文件位置
[root@test01 ~]# cp /usr/share/mysql/my-default.cnf /etc/my.cnf
初始化MySQL
[root@test01 init.d]# /usr/bin/mysql_install_db --help
Usage: /usr/bin/mysql_install_db [OPTIONS]
--basedir=path The path to the MySQL installation directory.
--builddir=path If using --srcdir with out-of-directory builds, you
will need to set this to the location of the build
directory where built files reside.
--cross-bootstrap For internal use. Used when building the MySQL system
tables on a different host than the target.
--datadir=path The path to the MySQL data directory.
If missing, the directory will be created, but its
parent directory must already exist and be writable.
--defaults-extra-file=name
Read this file after the global files are read.
--defaults-file=name Only read default options from the given file name.
--force Causes mysql_install_db to run even if DNS does not
work. In that case, grant table entries that
normally use hostnames will use IP addresses.
--help Display this help and exit.
--ldata=path The path to the MySQL data directory. Same as --datadir.
--no-defaults Don't read default options from any option file.
--keep-my-cnf Don't try to create my.cnf based on template.
Useful for systems with working, updated my.cnf.
Deprecated, will be removed in future version.
--random-passwords Create and set a random password for all root accounts
and set the "password expired" flag,
also remove the anonymous accounts.
--rpm For internal use. This option is used by RPM files
during the MySQL installation process.
--skip-name-resolve Use IP addresses rather than hostnames when creating
grant table entries. This option can be useful if
your DNS does not work.
--srcdir=path The path to the MySQL source directory. This option
uses the compiled binaries and support files within the
source tree, useful for if you don't want to install
MySQL yet and just want to create the system tables.
--user=user_name The login username to use for running mysqld. Files
and directories created by mysqld will be owned by this
user. You must be root to use this option. By default
mysqld runs using your current login name and files and
directories that it creates will be owned by you.
Any other options are passed to the mysqld program.
[root@test01 opt]# /usr/bin/mysql_install_db
Installing MySQL system tables...2016-06-27 21:45:36 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2016-06-27 21:45:36 0 [Note] /usr/sbin/mysqld (mysqld 5.6.31) starting as process 25243 ...
2016-06-27 21:45:36 25243 [Note] InnoDB: Using atomics to ref count buffer pool pages
2016-06-27 21:45:36 25243 [Note] InnoDB: The InnoDB memory heap is disabled
2016-06-27 21:45:36 25243 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2016-06-27 21:45:36 25243 [Note] InnoDB: Memory barrier is not used
2016-06-27 21:45:36 25243 [Note] InnoDB: Compressed tables use zlib 1.2.3
2016-06-27 21:45:36 25243 [Note] InnoDB: Using Linux native AIO
2016-06-27 21:45:36 25243 [Note] InnoDB: Using CPU crc32 instructions
2016-06-27 21:45:36 25243 [Note] InnoDB: Initializing buffer pool, size = 128.0M
2016-06-27 21:45:36 25243 [Note] InnoDB: Completed initialization of buffer pool
2016-06-27 21:45:36 25243 [Note] InnoDB: Highest supported file format is Barracuda.
2016-06-27 21:45:36 25243 [Note] InnoDB: 128 rollback segment(s) are active.
2016-06-27 21:45:36 25243 [Note] InnoDB: Waiting for purge to start
2016-06-27 21:45:36 25243 [Note] InnoDB: 5.6.31 started; log sequence number 1625987
2016-06-27 21:45:39 25243 [Note] Binlog end
2016-06-27 21:45:39 25243 [Note] InnoDB: FTS optimize thread exiting.
2016-06-27 21:45:39 25243 [Note] InnoDB: Starting shutdown...
2016-06-27 21:45:41 25243 [Note] InnoDB: Shutdown completed; log sequence number 1625997
OK

Filling help tables...2016-06-27 21:45:41 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2016-06-27 21:45:41 0 [Note] /usr/sbin/mysqld (mysqld 5.6.31) starting as process 25266 ...
2016-06-27 21:45:41 25266 [Note] InnoDB: Using atomics to ref count buffer pool pages
2016-06-27 21:45:41 25266 [Note] InnoDB: The InnoDB memory heap is disabled
2016-06-27 21:45:41 25266 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2016-06-27 21:45:41 25266 [Note] InnoDB: Memory barrier is not used
2016-06-27 21:45:41 25266 [Note] InnoDB: Compressed tables use zlib 1.2.3
2016-06-27 21:45:41 25266 [Note] InnoDB: Using Linux native AIO
2016-06-27 21:45:41 25266 [Note] InnoDB: Using CPU crc32 instructions
2016-06-27 21:45:41 25266 [Note] InnoDB: Initializing buffer pool, size = 128.0M
2016-06-27 21:45:41 25266 [Note] InnoDB: Completed initialization of buffer pool
2016-06-27 21:45:41 25266 [Note] InnoDB: Highest supported file format is Barracuda.
2016-06-27 21:45:41 25266 [Note] InnoDB: 128 rollback segment(s) are active.
2016-06-27 21:45:41 25266 [Note] InnoDB: Waiting for purge to start
2016-06-27 21:45:41 25266 [Note] InnoDB: 5.6.31 started; log sequence number 1625997
2016-06-27 21:45:41 25266 [Note] Binlog end
2016-06-27 21:45:41 25266 [Note] InnoDB: FTS optimize thread exiting.
2016-06-27 21:45:41 25266 [Note] InnoDB: Starting shutdown...
2016-06-27 21:45:43 25266 [Note] InnoDB: Shutdown completed; log sequence number 1626007
OK

To start mysqld at boot time you have to copy
support-files/mysql.server to the right place for your system

PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER !
To do so, start the server, then issue the following commands:

/usr/bin/mysqladmin -u root password 'new-password'
/usr/bin/mysqladmin -u root -h test01 password 'new-password'

Alternatively you can run:

/usr/bin/mysql_secure_installation

which will also give you the option of removing the test
databases and anonymous user created by default. This is
strongly recommended for production servers.

See the manual for more instructions.

You can start the MySQL daemon with:

cd /usr ; /usr/bin/mysqld_safe &

You can test the MySQL daemon with mysql-test-run.pl

cd mysql-test ; perl mysql-test-run.pl

Please report any problems at http://bugs.mysql.com/

The latest information about MySQL is available on the web at

http://www.mysql.com

Support MySQL by buying support/licenses at http://shop.mysql.com

WARNING: Found existing config file /usr/my.cnf on the system.
Because this file might be in use, it was not replaced,
but was used in bootstrap (unless you used --defaults-file)
and when you later start the server.
The new default config file was created as /usr/my-new.cnf,
please compare it with your file and take the changes you need.

WARNING: Default config file /etc/my.cnf exists on the system
This file will be read by default by the MySQL server
If you do not want to use this, either remove it, or use the
--defaults-file argument to mysqld_safe when starting the server
[root@test01 opt]# service mysql start
Starting MySQL. SUCCESS!
查看MySQL root用户的随机密码，并设置新密码
[root@test01 ~]# cat /root/.mysql_secret
# The random password set for the root user at Mon Jun 27 21:38:51 2016 (local time): gLAb5nUHcBqhKnk2
[root@test01 opt]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.31

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> set password=password('mysql');
Query OK, 0 rows affected (0.00 sec)

mysql> exit
Bye
允许远程登录设置
[root@test01 opt]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.6.31 MySQL Community Server (GPL)

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>
mysql> use mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

mysql> update user set host='%' where user='root' and host='localhost';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0

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

mysql> exit
Bye
设置开机启动
[root@test01 opt]# chkconfig mysql on
[root@test01 opt]# chkconfig --list |grep mysql
mysql 0:off 1:off 2:on 3:on 4:on 5:on 6:off
[root@test01 opt]# cd /var/lib/mysql --数据库目录
[root@test01 mysql]# ls
auto.cnf ib_logfile0 mysql performance_schema RPM_UPGRADE_MARKER-LAST test01.err
ibdata1 ib_logfile1 mysql.sock RPM_UPGRADE_HISTORY test test01.pid
[root@test01 mysql]# cd /usr/share/mysql/ --配置文件目录
[root@test01 mysql]# ls
binary-configure english hungarian mysqld_multi.server norwegian serbian
bulgarian errmsg-utf8.txt innodb_memcached_config.sql mysql-log-rotate norwegian-ny slovak
charsets estonian italian mysql_security_commands.sql polish spanish
czech fill_help_tables.sql japanese mysql.server portuguese swedish
danish french korean mysql_system_tables_data.sql romanian ukrainian
dictionary.txt german magic mysql_system_tables.sql russian
dutch greek my-default.cnf mysql_test_data_timezone.sql SELinux
/etc/init.d/mysql --启动脚本
[root@test01 mysql]# cd /usr/bin/ 相关命令目录
[root@test01 bin]# ls mysql*
mysql mysqlbug mysqld_multi mysql_fix_extensions mysql_secure_installation mysql_tzinfo_to_sql
mysqlaccess mysqlcheck mysqld_safe mysqlhotcopy mysql_setpermission mysql_upgrade
mysqlaccess.conf mysql_config mysqldump mysqlimport mysqlshow mysql_waitpid
mysqladmin mysql_config_editor mysqldumpslow mysql_install_db mysqlslap mysql_zap
mysqlbinlog mysql_convert_table_format mysql_find_rows mysql_plugin mysqltest

搜索

复制

秒客网

MySQL5.6主从复制方案

相关文章