MySQL充电_mysql 5.6.33主从+主主

最新推荐文章于 2021-02-05 01:59:19 发布

韩冰Bill

最新推荐文章于 2021-02-05 01:59:19 发布

阅读量94

点赞数

文章标签： MySQL充电

本文链接：https://blog.csdn.net/weixin_31026805/article/details/113415411

版权

1 实验环境

# cat /etc/redhat-release

CentOS Linux release 7.2.1511 (Core)

A主机：master IP：192.168.1.138 MYSQL:# mysql -Vmysql Ver 14.14 Distrib 5.6.33

B主机：slave IP：192.168.1.9 MYSQL:# mysql -Vmysql Ver 14.14 Distrib 5.6.33

注意:mysql数据库的版本，两个数据库版本要相同，或者slave比master版本低！

2 mysql5.6.33 安装：

1 下载mysql的rpm安装包[root@localhost mysql]# ls

MySQL-server-5.6.33-1.el6.x86_64.rpm

MySQL-client-5.6.33-1.el6.x86_64.rpm

MySQL-devel-5.6.33-1.el6.x86_64.rpm

2 检查MySQL及相关RPM包，是否安装，如果有安装，则移除(rpm –e 名称)[root@localhost mysql]# rpm -qa | grep -i mysql

[root@localhost mysql]#

[root@localhost mysql]# rpm -ivh MySQL-server-5.6.33-1.el6.x86_64.rpm

warning: MySQL-server-5.6.33-1.el6.x86_64.rpm: Header V3 DSA/SHA1 Signature, key ID 5072e1f5: NOKEY

error: Failed dependencies:

net-tools is needed by MySQL-server-5.6.33-1.el6.x86_64

[root@localhost mysql]# yum -y install net-tools

[root@localhost mysql]# rpm -ivh MySQL-server-5.6.33-1.el6.x86_64.rpm

file /usr/share/mysql/charsets/swe7.xml from install of MySQL-server-5.6.33-1.el6.x86_64 conflicts with file from package mariadb-libs-1:5.5.44-2.el7.centos.x86_64如果报错，就是删除mariadb的包)[root@localhost mysql]# yum remove mariadb-libs

[root@localhost mysql]# rpm -ivh MySQL-server-5.6.33-1.el6.x86_64.rpm

[root@localhost mysql]# rpm -ivh MySQL-devel-5.6.33-1.el6.x86_64.rpm

[root@localhost mysql]# rpm -ivh MySQL-client-5.6.33-1.el6.x86_64.rpm

[root@localhost mysql]# cp /usr/share/mysql/my-default.cnf /etc/my.cnf

[root@localhost mysql]# mkdir /mysqldata

[root@localhost mysql]# chown mysql:mysql /mysqldata

[root@localhost mysql]# /usr/bin/mysql_install_db

[root@localhost mysql]# cat /root/.mysql_secret

# The random password set for the root user at Tue Mar 21 01:12:42 2017 (local time): a64DA4mpLbeCaydf (5.6安装的默认密码不在是空，而是随机密码，自己要修改)

root@localhost mysql]# /etc/init.d/mysql restart

[root@localhost mysql]# mysql -uroot -pa64DA4mpLbeCaydf

mysql> set password = password('123456'); 修改root的密码

Query OK, 0 rows affected (0.00 sec)

mysql> exit

[root@localhost mysql]# mysql -uroot -p123456

2.1 修改数据的data目录，配置服务器的my.cnf文件：(如果需要修改datadir，请注意SELinux和目录是否有mysql用户的权限)/var/lib/mysql/ #数据库目录

/usr/share/mysql #配置文件目录

/usr/bin #相关命令目录

/etc/init.d/mysql #启动脚本#mkdir -p /mysql/data

#chown -R mysql:mysql /mysql/data

#cp -ar /var/lib/mysql/* /mysql/data/

#vim /etc/my.cnf

datadir = /mysqldata/data

port = 3306

socket = /mysqldata/data/mysql.sock

[root@localhost mysql]# /etc/init.d/mysql restart

Shutting down MySQL.. SUCCESS!

Starting MySQL. SUCCESS!

改了datadir和socket，重启成功，但是连接报错

[root@localhost mysql]# mysql -uroot -p

Enter password:

ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2)

[root@localhost mysql]# ss -tnlp | grep 3306

LISTEN 0 80 :::3306 :::* users:(("mysqld",pid=9413,fd=10))

这个时候在配置文件中加以下配置：

[client]

socket = /mysqldata/data/mysql.sock

[root@localhost mysqldata]# /etc/init.d/mysql restart

Shutting down MySQL.. SUCCESS!

Starting MySQL. SUCCESS!

3 在主服务器为从服务器设置一个连接账户，该账号必须授予REPLICATION SLAVE权限。

如果账户仅用于复制(推荐这样做),则不需要再授予其它任何权限。# mysql -uroot -p123456

mysql> grant replication slave on *.* to 'user'@'slave_ip' identified by '123456';

#mysql>grant file on *.* to 'user'@'slave_ip' IDENTIFIED BY 'mysql password';

mysql>flush privileges;

mysql> show databases;

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

| Database |

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

| information_schema |

| apacex |

| mysql |

| performance_schema |

| test |

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

创建一个测试数据库test1：mysql> create database test1;

mysql> use test1;

mysql> create table users(id int NOT NULL,name VARCHAR(100) NOT NULL);

mysql> insert into users values(1,"user1");

mysql> insert into users values(2,"user3");

mysql> flush privileges;

mysql> select * from users;

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

| id | name |

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

| 1 | user1 |

| 2 | user3 |

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

3.1 编辑主配置文件my.cnfserver_id = 1 #server-id用于标识唯一的数据库，这里设置为1

character_set_server = utf8

max_connections = 3000

skip-name-resolve

slow_query_log = TRUE

slow_query_log_file = /home/db/slow.log

long_query_time = 5

log-bin = mysql-bin

binlog_format = ROW

binlog-do-db = xxx ##可以被从服务器复制的库。

# binlog-ignore-db = yyy 不可以被从服务器复制的库

innodb_file_per_table = OFF

open_files_limit = 65535

back_log = 600

max_connect_errors = 6000

sql_mode = NO_ENGINE_SUBSTITUTION

auto_increment_increment = 2

auto_increment_offset = 1# touch /mysql/data/slow.log

# chown mysql:mysql /mysql/data/slow.log

[root@localhost data]# /etc/init.d/mysql restart

Shutting down MySQL.. SUCCESS!

Starting MySQL. SUCCESS!

登陆mysql，查看主的状态；

mysql> show master status;

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

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

| mysql-bin.000001 | 120 | | | |

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

4 配置从服务器：vim /etc/my.cnf

character_set_server = utf8

max_connections = 3000

skip-name-resolve

slow_query_log = TRUE

slow_query_log_file = /home/db/slow.log

long_query_time = 5

log-bin = mysql-bin

binlog_format = ROW

innodb_file_per_table = OFF

open_files_limit = 65535

back_log = 600

max_connect_errors = 6000

server_id = 2

sql_mode = NO_ENGINE_SUBSTITUTION

auto_increment_increment = 2

auto_increment_offset = 2

replicate-do-db = test1 要接收的库# /etc/init.d/mysql restart

Shutting down MySQL.. SUCCESS!

Starting MySQL..^[[A. SUCCESS!

# mysql -uroot -p123456

change master to master_host='192.168.1.28',master_user='root',master_password='abc-123',master_log_file='mysql-bin.000001', master_log_pos=120;mysql> start slave; #开启Slave

mysql> show slave status\G;

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

Slave_IO_State: Waiting for master to send event

Master_Host: 192.168.1.28

Master_User: hm

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: mysql-bin.000004

Read_Master_Log_Pos: 120

Relay_Log_File: localhost-relay-bin.000013

Relay_Log_Pos: 283

Relay_Master_Log_File: mysql-bin.000004

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

Replicate_Do_DB: students

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

Relay_Log_Space: 460

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: 71a3a3a2-0d99-11e7-b0de-000c290c49b2

Master_Info_File: /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: 01 row in set (0.13 sec)

5 测试：

主：mysql> use students;

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> insert into users values (3,"c");

Query OK, 1 row affected (0.01 sec)

mysql> select * from users;

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

| id | name |

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

| 1 | a |

| 2 | b |

| 3 | c |

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

3 rows in set (0.00 sec)

看从库：mysql> use students;

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> select * from users;

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

| id | name |

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

| 1 | a |

| 2 | b |

| 3 | c |

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

3 rows in set (0.00 sec)

到此为止主从就做好了，现开始做主主了：

1 在原从服务器上授权：mysql> grant replication slave on *.* to 'hm100'@'192.168.1.28' identified by '123456';

Query OK, 0 rows affected (0.03 sec)

1.1 编辑配置文件binlog-do-db = students 加要同步的数据库

1.2 重启# /etc/init.d/mysql restart

Shutting down MySQL... SUCCESS!

Starting MySQL..... SUCCESS!

mysql -uroot -p

mysql> show master status;

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

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

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

1 row in set (0.00 sec)

2 在原主上添加：vim /etc/my.cnf

添加：

replicate-do-db = xxx 要接收的库

# /etc/init.d/mysql restart

Shutting down MySQL... SUCCESS!

Starting MySQL..... SUCCESS!mysql -uroot -p

mysql> change master to master_host='192.168.1.166',master_user='hm100',master_password='abc-123',master_log_file='mysql-bin.000007', master_log_pos=120;

mysql> start slave;

mysql> show slave status\G;

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

Slave_IO_State: Waiting for master to send event

Master_Host: 192.168.1.166

Master_User: hm100

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: mysql-bin.000007

Read_Master_Log_Pos: 120

Relay_Log_File: localhost-relay-bin.000004

Relay_Log_Pos: 283