Mysql数据库添加从库,主从同步

• 环境准备
• 更改my.cnf
• 开启slave服务

查看已有mysql的版本

mysql -uroot -p

>select version();

下载相对应的mysql版本：

下载地址:http://dev.mysql.com/downloads/mysql

1）解压

#解压

tar-zxvf mysql-5.6.35-linux-glibc2.5-x86_64.tar.gz

#复制解压后的mysql目录

cp-r mysql-5.6.35-linux-glibc2.5-x86_64 /usr/local/mysql

?

2）添加用户组和用户

#添加用户组

groupadd mysql

#添加用户mysql 到用户组mysql

useradd -g mysql mysql

?

3）安装

cd /usr/local/mysql/

mkdir /data/mysql

chown -R mysql:mysql /usr/local/mysql

https://blog.csdn.net/qq_33004309/article/details/scripts/mysql_install_db --user=mysql --datadir=/usr/local/mysql/data/mysql

cp support-files/mysql.server /etc/init.d/mysqld

chmod 755 /etc/init.d/mysqld

cp support-files/my-default.cnf /etc/my.cnf

#修改启动脚本

vi /etc/init.d/mysqld

#修改项：

basedir=/usr/local/mysql/

datadir=/usr/local/mysql/data/mysql

#启动服务

service mysqld start

#测试连接

https://blog.csdn.net/qq_33004309/article/details/mysql/bin/mysql -uroot

#加入环境变量，编辑 /etc/profile，这样可以在任何地方用mysql命令了

export PATH=$PATH:/usr/local/mysql//bin

source /etc/profile

?

#创建root用户并授权

>GRANT ALL PRIVILEGES ON *.* TO ‘root‘@‘%‘ IDENTIFIED BY ‘password‘ WITH GRANT OPTION;

>GRANT ALL PRIVILEGES ON *.* TO ‘root‘@‘localhost‘ IDENTIFIED BY ‘password‘ WITH GRANT OPTION;

>flush privileges;

?

#启动mysql

service mysqld start

#关闭mysql

service mysqld stop

#查看运行状态

service mysqld status

?

mysql_Master：10.150.10.152:3306

mysql_Slave：10.150.10.153:3306

Master端配置：

[mysqld]

datadir=/var/lib/mysql

socket=/var/lib/mysql/mysql.sock

character-set-server=utf8

log-bin=mysql-bin

binlog-format=ROW

server_id=1

max_connections=500

# Disabling symbolic-links is recommended to prevent assorted security risks

symbolic-links=0

# Recommended in standard MySQL setup

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

transaction-isolation=Read-Committed

[mysqld_safe]

log-error=/var/log/mysqld.log

pid-file=/var/run/mysqld/mysqld.pid

character_set_server=utf8

?

?

Slave端配置：

?

[mysqld]

datadir=/var/lib/mysql

socket=/var/lib/mysql/mysql.sock

character-set-server=utf8

log-bin=mysql-bin

binlog-format=ROW

server_id=2

#skip-grant-tables

?

# Disabling symbolic-links is recommended to prevent assorted security risks

symbolic-links=0

?

# Recommended in standard MySQL setup

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

transaction-isolation=Read-Committed

?

[mysqld_safe]

log-error=/var/log/mysqld.log

pid-file=/var/run/mysqld/mysqld.pid

character_set_server=utf8

注意：my.cnf中server_id必须不一致！

Master端：

创建同步账号

>GRANT REPLICATION SLAVE ON *.* to ‘sync‘@‘%‘ identified by ‘p123456‘ WITH GRANT OPTION;

>flush privileges;

在把Master端的历史数据同步到Slave端之前，查询下bin_log位置：

>show master status;

在shell环境中执行：

sudo mysqldump -uroot -p --all-databases? > /tmp/allbak_152.sql

把Master端数据导出成.sql文件，并传到Slave端：

scp /tmp/allbak_152.sql 10.150.10.153:/tmp/

?

Slave端：

同步历史数据

>source /tmp/allbak_152;

同步数据

>change master to master_host=‘10.150.10.152‘,master_user=‘sync‘,master_password=‘p123456‘,master_log_file=‘mysql-bin.000104‘,master_log_pos=141856463;

开启同步及查看状态：

>start slave

>show slave status\G

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

Slave_IO_State: Waiting for master to send event

? Master_Host: 10.150.10.152

? Master_User: sync

? Master_Port: 3306

? Connect_Retry: 60

? Master_Log_File: mysql-bin.000104

? Read_Master_Log_Pos: 151852665

Relay_Log_File: mysqld-relay-bin.000044

? Relay_Log_Pos: 151852828

? Relay_Master_Log_File: mysql-bin.000104

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

? Relay_Log_Space: 151853049

? 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: dca1018d-b3b8-11e7-ac20-801844e3234c

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

1 row in set (0.02 sec)

?

slave操作：

start slave;开启同步

stop slave;关闭同步

reset slave;#重置

show slave status\G查看同步状态

?

注意：导入历史数据时，选择业务数据更新慢或者停掉的时候，如果slave状态有问题，在Slave端mysql执行

set?global?sql_slave_skip_counter=1;忽略一条错误

尝试同步到相同bin_log位置。

?

ok！