mysql实验前准备_mysql 主从同步实验细解

centos6.8安装mysql5.7

环境：

master:172.17.165.245

slave   :172.17.165.230

mysql 版本:mysql5.7

安装前准备

下载rpm文件wget https://dev.mysql.com/get/mysql57-community-release-el6-9.noarch.rpm

安装mysql 的yum 源rpm -Uvh mysql57-community-release-el6-9.noarch.rpm

或者

yum localinstall -y mysql57-community-release-el6-9.noarch.rpm

安装成功后在/etc/yum.repos.d/会多出几个mysql源

主服务器进行配置

1.yum 进行安装

yum install mysql-community-server -y

2.启动数据库[root@iZ2zegc312qxcxs61cotxlZ ~]# /etc/init.d/mysqld  start

Initializing MySQL database:                               [  OK  ]

Starting mysqld:                                           [  OK  ]

3.查看root 超级密码[root@iZ2zegc312qxcxs61cotxlZ ~]# grep 'temporary password' /var/log/mysqld.log

2018-02-06T05:39:06.661693Z 1 [Note] A temporary password is generated for root@localhost: a;HrlAlBh6Hk

3.修改mysql 用户密码mysql> show databases;

ERROR 1820 (HY000): Unknown error 1820

报错：MySQL5.7 加强了安全保障，以上意思是密码不符合安全策略要求，我们输入一个8位或以上长度，复杂一点的密码。

查看mysql5.7的密码策略报错是密码过期

mysql配置文件my.ini的mysqld下面添加一句default_password_lifetime=0重启mysql服务就行了

mysql> alter  user 'root'@'%' identified by '#20as3SElksds0ew98';

Query OK, 0 rows affected (0.00 sec)

开启远程链接

GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY '#20as3SElksds0ew99' WITH GRANT OPTION;

FLUSH PRIVILEGES;

4.修改配置文件my..cnf[client]

port = 3306

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

default-character-set = utf8mb4

[mysql]

prompt="MySQL [\d]> "

no-auto-rehash

[mysqld]

port = 3306

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

basedir = /usr/local/mysql

datadir = /data/mysql

pid-file = /data/mysql/mysql.pid

user = mysql

bind-address = 0.0.0.0

server-id = 1

binlog-do-db = kanfa_news_dev #同步数据库

binlog-ignore-db = mysql

binlog-ignore-db = performance_schema

binlog-ignore-db = information_schema

init-connect = 'SET NAMES utf8mb4'

character-set-server = utf8mb4

skip-name-resolve

#skip-networking

back_log = 300

max_connections = 5360

max_connect_errors = 6000

open_files_limit = 65535

table_open_cache = 1024

max_allowed_packet = 500M

binlog_cache_size = 1M

max_heap_table_size = 8M

tmp_table_size = 128M

read_buffer_size = 2M

read_rnd_buffer_size = 8M

sort_buffer_size = 8M

join_buffer_size = 8M

key_buffer_size = 256M

thread_cache_size = 64

query_cache_type = 1

query_cache_size = 64M

query_cache_limit = 2M

ft_min_word_len = 4

log_bin = mysql-bin

binlog_format = mixed

expire_logs_days = 7

log_error = /data/mysql/mysql-error.log

slow_query_log = 1

long_query_time = 1

slow_query_log_file = /data/mysql/mysql-slow.log

performance_schema = 0

explicit_defaults_for_timestamp

#lower_case_table_names = 1

skip-external-locking

default_storage_engine = InnoDB

#default-storage-engine = MyISAM

innodb_file_per_table = 1

innodb_open_files = 500

innodb_buffer_pool_size = 1024M

innodb_write_io_threads = 4

innodb_read_io_threads = 4

innodb_thread_concurrency = 0

innodb_purge_threads = 1

innodb_flush_log_at_trx_commit = 2

innodb_log_buffer_size = 2M

innodb_log_file_size = 32M

innodb_log_files_in_group = 3

innodb_max_dirty_pages_pct = 90

innodb_lock_wait_timeout = 120

bulk_insert_buffer_size = 8M

myisam_sort_buffer_size = 64M

myisam_max_sort_file_size = 10G

myisam_repair_threads = 1

interactive_timeout = 28800

wait_timeout = 28800

[mysqldump]

quick

max_allowed_packet = 500M

[myisamchk]

key_buffer_size = 256M

sort_buffer_size = 8M

read_buffer = 4M

write_buffer = 4M

重新启动数据库

mysqladmin -uroot -p shutdown  #(一定要使用这句)

查看二进制日志状态

mysql> show variables like 'log_bin';

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

| Variable_name | Value |

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

| log_bin       | ON    |

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

1 row in set (0.00 sec)

查看用户

mysql> use mysql;

mysql> select Host,User from user ;

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

| Host      | User          |

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

| localhost | mysql.session |

| localhost | mysql.sys     |

| localhost | root          |

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

3 rows in set (0.00 sec)

5.创建需要同步的数据库及用户；mysql> create database kanfa_news_dev;

Query OK, 1 row affected (0.00 sec)

mysql> CREATE USER 'slave'@'%' IDENTIFIED BY '#IjRpey35FDrFB2dp';

6.授权从服务器用户可以登录(主库创建用户  可以登录到从库上面)grant replication slave on *.* to slave@'%' identified by "#IjRpey35FDrFB2dp";

7 查看master 数据库状态mysql> show master status;

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

| File                    | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |

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

| mysql-bin-master.000001 |      154 | HA           | mysql            |                   |

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

1 row in set (0.00 sec)

从服务器进行安装配置

1.从服务安装mysql

2.修改配置文件，与主相同 server-id = 2

3.从服务器登录数据测试链接是否成功[root@iZ2zejbqrpwrmr4mjy0u9fZ ~]# mysql -uslave -p -h172.17.165.245

Enter password:

Welcome to the MySQL monitor.  Commands end with ; or \g.

Your MySQL connection id is 40

Server version: 5.7.21-log MySQL Community Server (GPL)

4.从数据库root密码root@iZ2zejbqrpwrmr4mjy0u9fZ ~]# grep 'temporary password' /var/log/mysqld.log

2018-02-06T07:46:31.989937Z 1 [Note] A temporary password is generated for root@localhost: fl54e1_FBAq8

mysql>alter  user 'root'@'%' identified by '#20as3SElksds0ew99';

ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.

mysql> SET PASSWORD = PASSWORD('#20as3SElksds0ew99');

Query OK, 0 rows affected, 1 warning (0.00 sec)

5.告知从数据库二进制日志文件位置(从库上面执行)mysql> change master to MASTER_HOST='172.17.165.245', MASTER_USER='slave',MASTER_PASSWORD='#IjRpey35FDrFB2dp',MASTER_LOG_FILE='mysql-bin.000005',MASTER_LOG_POS=154;

6.开启从数据库mysql>start slave;

7.查看master、slave状态mysql> show master status;

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

| File                    | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |

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

| mysql-bin-master.000001 |     1573 | kanfa_news_dev           | mysql            |                   |

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

1 row in set (0.00 sec)

mysql> show slave status\G

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

Slave_IO_State: Waiting for master to send event

Master_Host: 172.17.165.245

Master_User: slave

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: mysql-bin-master.000001

Read_Master_Log_Pos: 1573

Relay_Log_File: iZ2zejbqrpwrmr4mjy0u9fZ-relay-bin.000002

Relay_Log_Pos: 1062

Relay_Master_Log_File: mysql-bin-master.000001

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

Replicate_Do_DB:

Replicate_Ignore_DB:

#当看到Slave_IO_Running: YES、Slave_SQL_Running: YES才表明状态正常

注意：

当同步数据库的时候，需要先把主上面的数据库备份到从库上面，然后停止从  stop slave  导入数据库，然后启动从，插入数据进行核实。