第2章 mysql安装和配置,第六章：Mysql5.6主从搭建

CentOS 7 yum安装MySQL5.6

rpm -Uvh http://dev.mysql.com/get/mysql-community-release-el7-5.noarch.rpm

yum -y install mysql-community-server

systemctl enable mysqld

systemctl start mysqld

初始化：

mysql_secure_installation 一路回车

创建数据库

配置创建需要同步的数据库cattle。

#mysql

mysql>CREATE DATABASE IF NOT EXISTS cattle COLLATE='utf8_general_ci' CHARACTER SET='utf8';

mysql>GRANT ALL ON cattle.*TO 'cattle'@'%' IDENTIFIED BY 'cattle';

mysql>GRANT ALL ON cattle.*TO 'cattle'@'localhost' IDENTIFIED BY 'cattle';

grant all privileges on *.* to 'test'@'%' identified by 'test' with grant option;

在主数据库创建同步命令

grant replication slave on *.* to 'slaveuser'@'%' identified by '123456';

FLUSH PRIVILEGES;

锁主库表

mysql> FLUSH TABLES WITH READ LOCK;

显示主库信息

mysql> SHOW MASTER STATUS;

另开一个终端，打包主库

cd /var/lib/mysql

tar czvf cattle.tar.gz cattle

拷贝到另外机器

scp cattle.tar.gz root@192.168.177.183:/var/lib/mysql/

mysql> UNLOCK TABLES;

service mysqld stop

修改/etc/my.cnf

添加主

[mysqld]

server-id=130

log_bin=mysql-bin.000002 自己定义

保存

在/var/lib/mysql/会看到mysql-bin.000002.index 这个很重要

查看刚才创建的用户授权结果:

命令:

切换至mysql数据库

mysql> use mysql

mysql>select * from user where host='%' and user='slaveuser' \G;

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

Host: %

User: slaveuser

Password: *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9

Select_priv: N

Insert_priv: N

Update_priv: N

Delete_priv: N

Create_priv: N

Drop_priv: N

Reload_priv: N

Shutdown_priv: N

Process_priv: N

File_priv: N

Grant_priv: N

References_priv: N

Index_priv: N

Alter_priv: N

Show_db_priv: N

Super_priv: N

Create_tmp_table_priv: N

Lock_tables_priv: N

Execute_priv: N

Repl_slave_priv: Y

Repl_client_priv: N

Create_view_priv: N

Show_view_priv: N

Create_routine_priv: N

Alter_routine_priv: N

Create_user_priv: N

Event_priv: N

Trigger_priv: N

Create_tablespace_priv: N

ssl_type:

ssl_cipher:

x509_issuer:

x509_subject:

max_questions: 0

max_updates: 0

max_connections: 0

max_user_connections: 0

plugin: mysql_native_password

authentication_string:

password_expired: N

1 row in set (0.00 sec)

ERROR:

No query specified

Repl_slave_priv项为Y，表示授权成功

2.5查看master数据库状态

注意:查看之前做好数据库只读操作,防止在配置过程中出现写入数据日志，导致查询的master日志不可控

查看master数据库状态

命令:

mysql> show master status;

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

| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |

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

| mysql-bin.000002 | 6187 | | mysql | |

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

1 row in set (0.01 sec)

mysql>

日志文件名:称 mysql-bin.000002

日志文件位置: 6187

配置从数据库slave

如果master中需要同步的数据库已经存在且有数据,则需要将master中的数据库导出到slave保持master和slave需要同步的数据库信息一致。

3.1停止slave数据库服务

命令:

service mysqld stop

3.2修改/etc/my.cnf配置文件

在[mysqld]的节点下添加以下配置:

server-id=133

注意ID不能重复

stop slave;

配置master信息:

mysql>change master to

master_host='192.168.177.182',

master_user='slaveuser',

master_password='123456',

master_log_file='mysql-bin.000002',

master_log_pos=402;

配置说明:

master_host:master的主机地址

master_user:master上创建的同步数据用户,之前创建的slaveuser

master_password:master创建的同步数据用户密码

master_log_file:master最后一步查看的日志文件名

master_log_pos:master最后一步查看的日志当前位置,同步将从这个点开始

start slave;

mysql> show slave status \G;

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

Slave_IO_State: Waiting for master to send event

Master_Host: 192.168.8.202

Master_User: slaveuser

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: mysql-bin.000002

Read_Master_Log_Pos: 23305

Relay_Log_File: mysqld-relay-bin.000002

Relay_Log_Pos: 283

Relay_Master_Log_File: mysql-bin.000002

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

Relay_Log_Space: 457

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

Master_UUID: 4aed5689-6c69-11e7-9b1f-000c290e4f3d

Master_Info_File: /var/lib/mysql/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

注意,上面两个配置

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

Yes标识已经成功配置

验证主从同步

在主库server-1中创建一个表

mysql> USE cattle;

Database changed

mysql> CREATE TABLE `test` (`name` varchar(10) NULL ,`old` char(10) NULL );

Query OK, 0 rows affected (0.00 sec)

mysql> DESC test;

从库查询是否有这个新表

mysql> USE cattle;

Database changed

mysql> DESC test;

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

| Field | Type | Null | Key | Default | Extra |

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

| name | varchar(10) | YES | | NULL | |

| old | char(10) | YES | | NULL | |

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

2 rows in set (0.01 sec)