當前位置：首頁 > 运维知识 > 数据库 >内容正文

数据库

普通用户Mysql 5.6.13 主从，主主及nagios的mysql slave监控

發布時間：2025/3/13 数据库 25 豆豆

生活随笔收集整理的這篇文章主要介紹了普通用户Mysql 5.6.13 主从，主主及nagios的mysql slave监控小編覺得挺不錯的,現在分享給大家,幫大家做個參考.

Master:192.168.209.19

Slave:192.168.209.20

mysql版本:mysql5.6.13

1. 以root身份創建普通用戶,如mysql,并創建mysql安裝目錄:

?? # useradd mysql

?? # passwd mysql

?? # mkdir /mysql

?? # chown mysql:mysql /mysql

安裝依賴包：

yum?-y?install??gcc?gcc-c++?autoconf?automake?zlib*?fiex*?libxml*?ncurses-devel?libmcrypt*?libtool-ltdl-devel*?make?cmake

2. 下載MySQL5.6.13完整開發版, 并將其拷貝到/mysql目錄下.

?? 文件: mysql-5.6.13.tar.gz

3. 以mysql用戶解包MySQL,并將MySQL解包后的目錄更改為/mysql/:

?? # su - mysql

?? $ cd

?? $ mv mysql-5.6.13.tar.gz /mysql

?? $ tar xvzf mysql-5.6.13.tar.gz

4. 配置MySQL:

?? $ cd /mysql/5.6.13

$cmake -DCMAKE_INSTALL_PREFIX=/mysql/mysql-5.6

$make

$make install

$cd /mysql/mysql-5.6/scripts

$ ./mysql_install_db --user=mysql --basedir=/mysql/mysql-5.6 --datadir=/mysql/mysql-5.6/data

#cp /mysql/mysql-5.6/support-files/mysql.server /etc/init.d/mysql

#cp /mysql/mysql-5.6/support-files/my-default.cnf /etc/my.cnf

啟動mysql

$/mysql/mysql-5.6/support-files/mysql.server start

error解決：

[ERROR] Fatal error: Can't open and lock privilege tables: Table 'mysql.user' doesn't exist

初始化數據庫就ok，即：./mysql_install_db --user=mysql --basedir=/mysql/mysql-5.6 --datadir=/mysql/mysql-5.6/data

5.配置主從：

5.1、主從服務器分別操作：

? 5.1.1、mysql版本一致

? 5.1.2、初始化表，啟動mysql

? 5.1.3、設置mysql root密碼，?/mysql/mysql-5.6/bin/mysqladmin -u root password ‘123456’

5.2、修改master:

?? #vi /etc/my.cnf

?????? [mysqld]

?????? log-bin=mysql-bin?? #啟用二進制日志

?????? server-id=20?????? #服務器ID標示，主從不一致

5.3、修改slave:

?? #vi /etc/my.cnf

?????? [mysqld]

?????? log-bin=mysql-bin?? #啟用二進制日志

?????? server-id=21? ? ? #服務器ID標示，主從不一致

5.2 登錄mysql master配置

#/mysql/mysql-5.6/bin/mysql -uroot -p123456?

?? mysql>GRANT REPLICATION SLAVE ON *.* to 'test'@'%' identified by '123456';?

#一般不用root帳號，“%”表示所有客戶端都可能連，只要帳號，密碼正確，此處可用具體客戶端IP代替，如192.168.209.20，加強安全。

6、登錄主服務器的mysql，查詢master的狀態

?? mysql>show master status;

?? +------------------+----------+--------------+------------------+

?? +------------------+----------+--------------+------------------+

?? | mysql-bin.000007 |????? 320 |????????????? |????????????????? |

?? +------------------+----------+--------------+------------------+

?? 1 row in set (0.00 sec)

?? 注：獲取file和position。

7、配置從服務器mysql Slave：

?? mysql>change master?to master_host='192.168.209.19',master_user='test',master_password='123456',master_log_file='mysql-bin.000007',master_log_pos=320;?? #320為主服務器的position，無單引號。

?? Mysql>start slave;??? //啟動從服務器復制功能

8、檢查slave狀態：

mysql> show slave status\G

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

?????????????? Slave_IO_State: Waiting for master to send event

????????????????? Master_Host: 192.168.209.19

????????????????? Master_User: mysync

????????????????? Master_Port: 3306

??????????????? Connect_Retry: 60

????????????? Master_Log_File: mysql-bin.000007

????????? Read_Master_Log_Pos: 320

?????????????? Relay_Log_File: cdn20-relay-bin.000002

??????????????? Relay_Log_Pos: 283

??????? Relay_Master_Log_File: mysql-bin.000007

???????????? 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: 320

????????????? Relay_Log_Space: 456

????????????? 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: 20

????????????????? Master_UUID: 6a36aa69-1c81-11e4-8213-b8ca3af2484f

???????????? Master_Info_File: /mysql/mysql-5.6/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: 0

1 row in set (0.00 sec)

Slave_IO及Slave_SQL進程YES狀態，否則都是錯誤的狀態(如：其中一個NO均屬錯誤)。

至此，主從服務器配置完成。

9、主從服務器測試：

主服務器Mysql，建立數據庫，并在這個庫中建表插入一條數據：

mysql> create database test1;

Query OK, 1 row affected (0.00 sec)

mysql> use test1

Database changed

mysql> create table test1(id int(4),name char(8));

Query OK, 0 rows affected (0.01 sec)

mysql> insert into test values(001,'test1');

ERROR 1146 (42S02): Table 'test1.test' doesn't exist

mysql> insert into test1 values(001,'test1');

Query OK, 1 row affected (0.00 sec)

mysql> show databases;

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

| Database?????????? |

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

| information_schema |

| mysql????????????? |

| performance_schema |

| test?????????????? |

| test1????????????? |

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

5 rows in set (0.00 sec)

從服務器Mysql查詢：

mysql> show databases;

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

| Database?????????? |

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

| information_schema |

| mysql????????????? |

| performance_schema |

| test?????????????? |

| test1????????????? |

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

5 rows in set (0.01 sec)

至此，mysql主從配置完成，可以正常使用；后續會加入集群模式。

10、nagios mysql slave 監控

因為網上沒完整的mysql集群監控插件，寫個簡單的腳本監控mysql主從，只需要傳入幾個簡單參數即可實現，腳本如下：

[root@host1 objects]# cat /usr/local/nagios/libexec/check_mysql_slave.sh

#!/bin/bash

host=$1

mysqluser=$2

mysqlpasswd=$3

mysqlport=$4

Slave_Yes=`mysql -h $host -u$mysqluser -p$mysqlpasswd -P $4 -e 'show slave status\G' | grep -c Yes`

??????? if [ $Slave_Yes -eq 2 ];then

??????????????? echo "Mysql slave is OK!"

??????? exit 0;

??????? else

??????? echo "Mysql slave is Error!";

??????? exit 2;

??????? fi

定義command.cfg

#check_mysql_slave

define command{

??????? command_name check_mysql_slave

??????? command_line $USER1$/check_mysql_slave.sh $HOSTADDRESS$ $USER7$ $USER8$ $USER9$

}

定義主機配置

define service{

??????? use???????????????????????????? local-service???????? ; Name of service template to use

??????? host_name?????????????????????? hostname

??????? service_description???????????? MySQL_AB

??????? check_command?????????????????? check_mysql_slave

??????? notifications_enabled?????????? 0

??????? }

同時，需要將mysqluser，mysqlpasswd，mysqlport都寫入到resource.cfg文件中

/usr/local/nagios/etc/resource.cfg

$USER7$=root

$USER8$=passwd

$USER9$=3306

配置完成之后，reload一下nagios配置即可。

具體監控如下：

=================MySQL 主主=================

MySQL主主復制，兩臺服務器的任何一臺上面的數據庫存發生了改變都會同步到另一臺服務器上，兩臺服務器互為主從，并且都能向外提供服務。

詳情如下：

server1:192.168.203.10

server2:192.168.203.20

OS:CentOS release 6.3 (Final) 64bit

mysql版本：mysql-5.6.13

一、創建并授權mysql用戶

這一步在每一臺（主）服務器上創建一個用戶，并為之授權，使它們可以互相訪問彼此的數據庫

server1

創建允許server2來訪問的用戶test2,密碼為：123456

mysql> GRANT REPLICATION SLAVE ON *.* TO 'test2'@'192.168.203.20' ?IDENTIFIED BY '123456';

server2

創建一個允許server1來訪問的用戶test1,密碼為：123456

mysql> GRANT REPLICATION SLAVE ON *.*? TO 'test1'@'192.168.203.10' ?IDENTIFIED BY '123456';

二、修改MySQL配置文件

MySQL的配置文件中修改/etc/my.cnf：

server1如下：

?[mysqld]

?server-id = 10

?log-bin = mysql-bin

?replicate-do-db = mysqldb

?auto-increment-increment = 2

?auto-increment-offset = 1

# service mysqld restart

server2如下：

?[mysqld]

?server-id = 20

?log-bin = mysql-bin

?replicate-do-db = mysqldb

?auto-increment-increment = 2

?auto-increment-offset = 2?

# service mysqld restart

參數解釋：

　　兩個配置文件只有server-id不同和auto-increment-offset不同

　　auto-increment-offset是用來設定數據庫中自動增長的起點的，因為服務器都設定了一次自動增長值2，所以它們的起點必須得不同，這樣才能避免兩臺服務器數據同步時出現主鍵沖突

　　replicate-do-db?指定同步的數據庫，mysqldb數據庫

　　auto-increment-increment的值應設為整個結構中服務器的總數，此實驗為兩臺服務器，所以值設為2

三、查詢數據庫的二進制日志和pos

　　主主模式，測試的話，可以先做一個數據庫，然后，讓第二個數據庫來將數據自動同步，故不需要備份數據或者是導出導入數據。?

server1數據庫

mysql> show master status;

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

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

| mysql-bin.000006 |??? 11230 |????????????? |????????????????? |?????????????????? |

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

1 row in set (0.00 sec)

server2數據庫

mysql> show master status;

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

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

| mysql-bin.000007 |??? 10426 |????????????? |????????????????? |?????????????????? |

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

1 row in set (0.00 sec)

五、說明二進制日志路徑

server1：

# mysql> change master to master_host='192.168.203.20',master_user='test2',master_password='123456',master_log_file='mysql-bin.000007',master_log_pos=10426;? ?

server2：

# mysql>change master to master_host='192.168.203.10',master_user='test2',master_password='123456',master_log_file='mysql-bin.000006',master_log_pos=11230;

六、啟動復制（Replication）功能

在兩臺數據庫上分別執行

# mysql> START SLAVE;

至此配置完成！

七、測試：

在任意一臺服務器上創建一個數據庫或者修改數據，對應的在另一臺服務器上查看數據庫數據。

轉載于:https://www.cnblogs.com/krainbow/p/3894154.html

總結

以上是生活随笔為你收集整理的普通用户Mysql 5.6.13 主从，主主及nagios的mysql slave监控的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。

上一篇： java的Comparator和Comp
下一篇： POJ 1185 炮兵阵地状压dp