centos 6.5 mysql主键_CentOS 6.5 下MySql主从、主主配置

最新推荐文章于 2021-02-06 15:17:18 发布

Mister.Pong

最新推荐文章于 2021-02-06 15:17:18 发布

阅读量79

点赞数

文章标签： centos 6.5 mysql主键

本文链接：https://blog.csdn.net/weixin_42509197/article/details/113293796

版权

参考网站：

1 MySql主从配置

主库所在服务器IP地址为192.168.178.2，从库所在服务器IP地址为192.168.178.3

1.1 MySql主从复制原理

mysql 主从同步原理：

1. master 将操作记录到二进制日志(binary log)中；

2. slave IO 线程将master的binary log events读写到它的中继日志(relay log)；

3. slave SQL进程读取中继日志，将重做记录数据到数据库中。

MySQL的主从同步是一个很成熟的架构，优点为：

①在从服务器可以执行查询工作(即我们常说的读功能)，降低主服务器压力；

②在从主服务器进行备份，避免备份期间影响主服务器服务；

③当主服务器出现问题时，可以切换到从服务器。

1.2 Mysql的安装

本文采用yum安装，命令如下：

[root@xldwhj ~]# yum install mysql mysql-devel mysql-server

1.3 主库设置

修改主库my.cnf,主要是设置个不一样的id和logbin(#这可依具体环境而定，压力大的化可采用huge.cnf)，红色部分为新增部分。

[root@xldwhj ~]# vim /etc/my.conf

[mysqld]

datadir=/var/lib/mysql

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

user=mysql

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

symbolic-links=0

log-bin=mysql-bin

server-id=1

binlog-ignore-db=information_schema

binlog-ignore-db=cluster

binlog-ignore-db=mysql

[mysqld_safe]

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

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

主库设置(2)：

port = 3306

server_id = 1

log-bin= mysql-bin

binlog_format = mixed

read-only=0

#binlog-do-db=test

binlog-ignore-db=mysql

binlog-ignore-db=information_schema

binlog-ignore-db=performance_schema

auto-increment-offset=1

auto-increment-increment=2

启动主库生效：

[root@xldwhj

~]# service mysqld restart

登陆主库：

[root@xldwhj

~]# mysql –u root –p

Enter password:

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

Your MySQL connection id is 242

Server version: 5.1.73-log Source

distribution

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>grant all privileges on *.* to '用户名'@'%'

identified by '密码'; //赋予从库权限帐号，

本文建立的账户名称为xldwhj，密码为123123

//(主从库设置用第二种方式时的设置方法)

mysql>grant replication slave on *.* to '用户名'@'192.168.1.153' identified by '密码';

允许用户在主库上读取日志，输入为从库设置的用户名和密码，本文创建的用户名为xldroot。

mysql>

select user,host from mysql.user;

//查看是否创建用户成功

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

| user

| host |

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

| xldroot

| % | //此处为从库新创建的用户。

| 用户名 | % |

| root

| 127.0.0.1 |

| localhost |

| root

| localhost |

| xldwhj |

| root

| xldwhj |

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

7 rows in set (0.00 sec)

锁主库表：

mysql> flush tables with read lock;

#现在禁止操作主数据库！防止日志有变化，保证主从数据初始状态一致！

7、显示主库信息

记录File和Position，从库设置将会用到

mysql> show master status;

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

| File | Position | Binlog_Do_DB |

Binlog_Ignore_DB |

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

| mysql-bin.000003 | 790 | |

information_schema,cluster,mysql ------|

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

1 row in set (0.00 sec)

至此主库设置完成

1.4

从库设置

在主库服务器上解锁主库表

mysql> unlock tables;

修改从库配置文件/etc/my.cof

[mysqld]

datadir=/var/lib/mysql

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

user=mysql

# Disabling symbolic-links is recommended

to prevent assorted security risks

symbolic-links=0

log-bin=mysql-bin

server-id=2

binlog-ignore-db=information_schema

binlog-ignore-db=cluster

binlog-ignore-db=mysql

replicate-do-db=test

replicate-ignore-db=mysql

log-slave-updates

slave-skip-errors=all

slave-net-timeout=60

master-host=192.168.178.2

master-user=root

master-password=pfingo

[mysqld_safe]

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

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

从库设置(2)

port = 3306

server_id = 2

log-bin= mysql-bin

binlog_format = mixed

read-only=0

#replicate-do-db=test

replicate-ignore-db=mysql

replicate-ignore-db=information_schema

replicate-ignore-db=performance_schema

relay_log=mysql-relay-bin

log-slave-updates=on

#auto-increment-offset=2

#auto-increment-increment=2

在从库服务器上验证连接主库：

[root@xldmysql

~]# mysql -u root -p

Enter password:

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

Your MySQL connection id is 269

Server version: 5.1.73-log Source

distribution

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> slave stop;

mysql> change master to

master_host='192.168.178.2',master_user='xldroot',master_password='123123',master_log_file='mysql-bin.000003', master_log_pos=790;

//连接MASTER MASTER_LOG_FILE为主库的File，MASTER_LOG_POS为主库的Position

//上述第二条命令语句中的master_log_file='mysql-bin.000003', master_log_pos=790;对应为前面在主库中执行的show master status;结果。

启动从库服务

mysql>

slave start;

主库从库配置文件部分说明：

log-bin ：需要启用二进制日志

server_id : 用于标识不同的数据库服务器

binlog-do-db : 需要记录到二进制日志的数据库

binlog-ignore-db : 忽略记录二进制日志的数据库

auto-increment-offset :该服务器自增列的初始值。

auto-increment-increment :该服务器自增列增量。

replicate-do-db ：指定复制的数据库

replicate-ignore-db ：不复制的数据库

relay_log ：从库的中继日志，主库日志写到中继日志，中继日志再重做到从库。

log-slave-updates ：该从库是否写入二进制日志，如果需要成为多主则可启用。只读可以不需要。

如果为多主的话注意设置

auto-increment-offset 和 auto-increment-increment

1.5

测试

在主库服务器上新建一张数据库表：

mysql>

use test;

Database changed

mysql>

CREATE TABLE `myTest` (

-> `id` INT( 5 ) UNSIGNED NOT NULL

AUTO_INCREMENT ,

-> `username` VARCHAR( 20 ) NOT NULL ,

-> `password` CHAR( 32 ) NOT NULL ,

`last_update` DATETIME NOT NULL ,

-> `number` FLOAT( 10 ) NOT NULL ,

-> `content` TEXT NOT NULL ,

-> PRIMARY KEY ( `id` )

-> ) ENGINE = MYISAM ;

Query OK, 0 rows affected (0.02 sec)

mysql>

show tables;

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

| Tables_in_test |

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

| myTest |

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

1 row in set (0.00 sec)

在从库查看主库新建的表是否存在：

mysql>

use test;

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_test |

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

| myTest |

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

1 row in set (0.01 sec)

可以看出从库与主库中信息一致，主从配置成功。

为了更进一步验证在从库上输入show slave status\G

mysql>

show slave status\G

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

***************************

Slave_IO_State: Waiting for

master to send event

Master_Host: 192.168.178.2

Master_User: xldroot

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: mysql-bin.000007

Read_Master_Log_Pos: 106

Relay_Log_File:

mysqld-relay-bin.000002

Relay_Log_Pos: 251

Relay_Master_Log_File: mysql-bin.000007

Slave_IO_Running:

Yes

Slave_SQL_Running: Yes

Replicate_Do_DB: test

Replicate_Ignore_DB: mysql

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

Relay_Log_Space: 407

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:

1 row in set (0.00 sec)

Slave_IO_Running: Yes(网络正常)；

Slave_SQL_Running: Yes(表结构正常)

进一步验证了以上过程的正确性。

MySql主主配置

采用第二种主从复制的my.cnf配置

192.168.178.2虚拟机的my.cnf配置如下：

[mysqld]

port = 3306

server_id = 1

log-bin= mysql-bin

binlog_format = mixed

read-only=0

#binlog-do-db=test

binlog-ignore-db=mysql

binlog-ignore-db=information_schema

binlog-ignore-db=performance_schema

auto-increment-offset=1

auto-increment-increment=2

#主主复制的192.168.178.2作为从库的设置(新增)

#replicate-do-db=test

replicate-ignore-db=mysql

replicate-ignore-db=information_schema

replicate-ignore-db=performance_schema

relay_log=mysql-relay-bin

log-slave-updates=on

192.168.178.3虚拟机的my.cnf的设置如下：

[mysqld]

basedir =/usr/local/mysql

datadir =/usr/local/mysql/data

port = 3306

server_id = 2

log-bin= mysql-bin

binlog_format = mixed

read-only=0

#replicate-do-db=test

replicate-ignore-db=mysql

replicate-ignore-db=information_schema

replicate-ignore-db=performance_schema

relay_log=mysql-relay-bin

log-slave-updates=on

#主主复制的192.168.178.3作为主库设置(新增)

#binlog-do-db=test

binlog-ignore-db=mysql

binlog-ignore-db=information_schema

binlog-ignore-db=performance_schema

auto-increment-offset=2

auto-increment-increment=2

添加完成后，重启数据库

[root@xldwhj ~]# service mysqld restart

Stopping

mysqld:

[ OK ]

Starting

mysqld: [

OK ]

在192.168.178.3原从库中创建192.168.178.2原主库用于连接的用户：

mysql>grant replication slave on *.* to 'xldroot'@'192.168.1.153'

identified by '123123';

查看192.168.178.3原从库的日志记录位置：

mysql> show master status\G

***************************

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

File: mysql-bin.000009

Position: 521

Binlog_Do_DB:

Binlog_Ignore_DB:

mysql,information_schema,performance_schema

1 row in set

(0.00 sec)

从192.168.178.2原主库连接到192.168.178.3原从库的命令如下：

mysql>

change master to master_host='192.168.178.3',master_user='xldroot',

master_password='123123',master_log_file='mysql-bin.000009',

master_log_pos=521;

查看192.168.178.2原主库的同步信息：

mysql> show slave status\G

***************************

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

Slave_IO_State: Waiting for

master to send event

Master_Host: 192.168.178.3

Master_User: repl_user

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: mysql-bin.000009

Read_Master_Log_Pos: 521

Relay_Log_File:

mysql-relay-bin.000004

Relay_Log_Pos: 499

Relay_Master_Log_File: mysql-bin.000009

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

Replicate_Do_DB:

Replicate_Ignore_DB:

mysql,information_schema,performance_schema

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

Relay_Log_Space: 654

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:

Last_IO_Errno: 0

Last_IO_Error:

Last_SQL_Errno: 0

Last_SQL_Error:

1 row in set

(0.00 sec)

双主配置已经配好，下面进行进一步的测试：

在192.168.178.3原从库上创建一张表并进行相关操作：

mysql>use test

mysql>create table tabdemo(id int primary key

auto_increment, value int default 0) auto_increment= 1 engine=innodb default

charset=utf8;

mysql>insert into tabdemo(value)

values(1),(1),(1),(1),(1);

mysql>select * from tabdemo;

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

| id | value

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

| 1 |

1 |

| 3 |

1 |

| 5 |

1 |

| 7 |

1 |

| 9 |

1 |

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

在192.168.178.2原主机上执行：

mysql>select * from tabdemo; //出现下述信息

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

| id | value

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

| 1 |

1 |

| 3 |

1 |

| 5 |

1 |

| 7 |

1 |

| 9 |

1 |

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

执行

mysql> insert into tabdemo(value)

values(2),(2),(2),(2),(2);

mysql> select * from tabdemo;

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

| id | value |

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

| 1 |

1 |

| 2 |

1 |

| 3 |

1 |

| 4 |

1 |

| 5 |

1 |

| 6 |

2 |

| 8 |

2 |

| 10 | 2 |

| 12 | 2 |

| 14 | 2 |

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

在192.168.178.3上执行

mysql> select * from tabdemo;

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

| id | value |

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

| 1 |

1 |

| 2 |

1 |

| 3 |

1 |

| 4 |

1 |

| 5 |

1 |

| 6 |

2 |

| 8 |

2 |

| 10 | 2 |

| 12 | 2 |

| 14 | 2 |

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

至此为止，测试完毕，双主配置成功。

Mister.Pong

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
centos 6.5 mysql主键_CentOS 6.5 下MySql主从、主主配置

参考网站：1 MySql主从配置主库所在服务器IP地址为192.168.178.2，从库所在服务器IP地址为192.168.178.31.1 MySql主从复制原理mysql 主从同步原理：1. master 将操作记录到二进制日志(binary log)中；2. slave IO 线程将master的binary log events读写到它的中继日志(rela...
复制链接

扫一扫