mariadb5.5 linux性能,一次mariadb-5.5.40主从环境搭建

传统上：一个主可以有多从，但是一个从服务器只有一个主

而今：一从多主

PS:服务器版本需要保持一致：如果不一致，从服务器版本必须高于主服务器！当然，最好版本相同

简单配置过程：

一．master

1，启用二进制日志

1，设置一个在当前集群中唯一的server-id

2，创建一个有复制权限的账号(REPLICATION

SLAVE ,REPLICATION CLIENT)

二．slave

1，启用中继日志

2，设置一个在当前集群中唯一的server-id

3，使用有复制权限用户账号连接至主服务器，并启动复制线程

插播：备份

Mysqldunmp实现备份是，由于本身对有些存储引荇的备份，如myisa只能从温备，innodb能够热备，备份需要启动一个大事务

Lvm快照，几乎热备，事先请求锁定数据库，创建快照，释放锁。工具perl脚本借助快照备份

Xtrabckup：单表导入导出，流式化传输备份，物理备份工具。真正使用需要考虑备份策略，如：全备加增量备份，全备加差异备份，xtrabackup本身支持增量，但是增量只会innodb备份，myisa是不支持的。默认mysql启用最好启用innodb

rmp包：

mariadb-5.5.40-linux-x86_64.tar.gz

一，主从复制

mkdir -pv /mydata/data

groupadd -r -g 306 mysql

useradd -r -g 306 -u 306 mysql

chown -R mysql.mysql /mydata

tar xf mariadb-5.5.40-linux-x86_64.tar.gz -C /usr/local

cd /usr/local

ln -sv mariadb-5.5.40-linux-x86_64 mysql

cd mysql/

chown -R root.mysql ./*

scripts/mysql_install_db --user=mysql --datadir=/mydata/data

mkdir /etc/mysql

cp support-files/my-large.cnf /etc/mysql/my.cnf

vim  /etc/mysql/my.cnf

datadir=/mydata/data

innodb_file_per_table = on

skip_name_resolve = on

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

chkconfig --add mysqld

service mysqld start

master配置：

vim  /etc/mysql/my.cnf

log-bin=master-bin  线上机器不能修改

server-id       = 1

master主机授权slave复制

/usr/local/mysql/bin/mysql

MariaDB [(none)]> GRANT REPLICATION CLIENT,REPLICATION SLAVE ON *.* TO 'repluser'@'192.168.%.%' IDENTIFIED BY 'replpass';

MariaDB [(none)]> FLUSH PRIVILEGES;

slave配置：

vim  /etc/mysql/my.cnf

server-id       = 20

relay-log = relay-bin  中继日志

read-only = on

master查看

MariaDB [(none)]> SHOW GLOBAL VARIABLES LIKE '%only';

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

| Variable_name | Value |

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

| read_only     | OFF   |

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

1 row in set (0.00 sec)

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

+read_only 是不能阻止所有人向里面写入数据的，只能组织普通账号

+

+组织所有人不能写的话，在配置文件中添加如下：

+read-only = on

+service mysqld restart

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

在配置之前查看下master的日志

MariaDB [(none)]> SHOW MASTER STATUS;

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

| File              | Position | Binlog_Do_DB | Binlog_Ignore_DB |

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

| master-bin.000002 |      497 |              |                  |

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

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

+因为是第一次复制，主从都是空的，指明从当前这一刻开始复制

+如果新加入的从服务器，而主服务器已经工作很久，这时候应备份主服务器的数据恢复到从服务器，在从备份的的位置向后复制

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

slave：

MariaDB [(none)]> CHANGE MASTER TO MASTER_HOST='192.168.131.139',MASTER_USER='repluser',MASTER_PASSWORD='replpass',MASTER_LOG_FILE='master-bin.000002',MASTER_LOG_POS=497,MASTER_CONNECT_RETRY=5,MASTER_HEARTBEAT_PERIOD=2;

Query OK, 0 rows affected (0.14 sec)

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

+CHANGE MASTER TO

+MASTER_HOST='192.168.131.139', MASTER主机ip

+MASTER_USER='repluser',     复制权限的用户

+MASTER_PASSWORD='replpass', 复制权限的用户的密码

+MASTER_LOG_FILE='master-bin.000001', 日志文件开始位置

+MASTER_LOG_POS=497,   日志文件数值(在master 数据库中使用SHOW MASTER STATUS;查看当前是多少便从多少开始！！如果新加入的从服务器，而主服务器已经工作很久，这时候应备份主服务器的数据恢复到从服务器，在从备份的的位置向后复制)

+MASTER_CONNECT_RETRY=5,     5秒钟复制一次

+MASTER_HEARTBEAT_PERIOD=2;  心跳信息时间间隔

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

完成后检测

MariaDB [(none)]> SHOW SLAVE STATUS\G

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

Slave_IO_State:

Master_Host: 192.168.131.139

Master_User: repluser

Master_Port: 3306

Connect_Retry: 5

Master_Log_File: master-bin.000001

Read_Master_Log_Pos: 497

Relay_Log_File: relay-bin.000001    已经开始

Relay_Log_Pos: 4

Relay_Master_Log_File: master-bin.000001

Slave_IO_Running: No       从服务器io线程

Slave_SQL_Running: No       从服务器sql线程

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

Relay_Log_Space: 245

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

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

1 row in set (0.00 sec)

启动slave io线程和sql线程

MariaDB [(none)]>START SLAVE;

Query OK, 0 rows affected (0.00 sec

而后查看slave的relay-bin.000001和relay-log.info已经存在

[root@mysql-slave ~]# ll /mydata/data/

-rw-rw---- 1 mysql mysql      245 Jul  4 23:26 relay-bin.000001

-rw-rw---- 1 mysql mysql       19 Jul  4 23:26 relay-bin.index

-rw-rw---- 1 mysql mysql       43 Jul  4 23:26 relay-log.info

drwx------ 2 mysql root      4096 Jul  4 22:09 test

[root@mysql-slave ~]#

master端：

MariaDB [(none)]> SHOW MASTER STATUS\G

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

File: master-bin.000002

Position: 677

Binlog_Do_DB:

Binlog_Ignore_DB:

1 row in set (0.00 sec)

MariaDB [(none)]>

slave端：

MariaDB [(none)]> SHOW SLAVE STATUS\G;

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

Slave_IO_State: Waiting for master to send event

Master_Host: 192.168.131.139

Master_User: repluser

Master_Port: 3306

Connect_Retry: 5

Master_Log_File: master-bin.000002

Read_Master_Log_Pos: 776

Relay_Log_File: localhost-relay-bin.000002

Relay_Log_Pos: 809

Relay_Master_Log_File: master-bin.000002

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

PS：在做mysql主从时，中间最好不要修改主机名。否则可能会出现Slave_IO_Running和Slave_SQL_Running起不来的情况！

MASTER：

MariaDB [(none)]> CREATE DATABASE markdb;

Query OK, 1 row affected (0.00 sec)

MariaDB [(none)]> use markdb

Database changed

MariaDB [markdb]> create table tn1 (id int);

Query OK, 0 rows affected (0.05 sec)

SLAVE：

MariaDB [(none)]> use markdb

Database changed

MariaDB [markdb]> show tables;

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

| Tables_in_markdb |

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

| tn1              |

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

1 row in set (0.06 sec)

帮助选项

MariaDB [markdb]> HELP CHANGE MASTER TO

option:

MASTER_BIND = 'interface_name'

| MASTER_HOST = 'host_name'   主服务器地址

| MASTER_USER = 'user_name'    有复制权限的用户名

| MASTER_PASSWORD = 'password'  用户密码

| MASTER_PORT = port_num      主服务器端口

| MASTER_CONNECT_RETRY = interval   链接重试的时间间隔

| MASTER_HEARTBEAT_PERIOD = interval 心跳检测的时间间隔

| MASTER_LOG_FILE = 'master_log_name' 主服务器二进制日志文件

| MASTER_LOG_POS = master_log_pos    二进制日志文件中的位置

| RELAY_LOG_FILE = 'relay_log_name'

| RELAY_LOG_POS = relay_log_pos

基于ssl复制使用的

| MASTER_SSL = {0|1}

| MASTER_SSL_CA = 'ca_file_name'

| MASTER_SSL_CAPATH = 'ca_directory_name'

| MASTER_SSL_CERT = 'cert_file_name'

| MASTER_SSL_KEY = 'key_file_name'

| MASTER_SSL_CIPHER = 'cipher_list'

| MASTER_SSL_VERIFY_SERVER_CERT = {0|1}

| IGNORE_SERVER_IDS = (server_id_list)   做环装复制时。

mysql环境变量：export PATH=/usr/local/mysql/bin:$PATH

除非另有说明，否则本站上的内容根据以下许可进行许可: CC署名-非商业性使用-相同方式共享4.0国际许可协议4.0进行许可

本文作者：www.linuxea.com for Mark

文章链接：http://www.linuxea.com/901.html (转载请注明本文出处和本章链接)