MARIADB Master-Slave synchronization

This article is a mariadb-10.0.17 version of the build: https://downloads.mariadb.org/interstitial/mariadb-10.0.17/source/ Mariadb-10.0.17.tar.gz/from/http%3a//ftp.hosteurope.de/mirror/archive.mariadb.org/?serve

master:192.168.1.166

slave:192.168.1.165

1. Create a MySQL general account, set the database to store data directory, set permissions

[[email protected] ~]# groupadd-r MySQL

[[Email protected]~]# useradd-r-G mysql-s/sbin/nologin MySQL

[Email protected]~]# mkdir-p/data/mydata

[Email protected]~]# chown-r Mysql:mysql/data

2. Install database dependent packages

[Email protected]~]# yum install-y gcc gcc-c++ make cmake ncurses ncurses libxml2 libxml2-developenssl-devel Bison bison -devel Ncurses-devel

3. Upload mariadb package, unzip, compile and install.

[Email protected] ~]# tar zxvf mariadb-10.0.17.tar.gz

[Email protected] mariadb-10.0.17]# Cmake-dcmake_install_prefix=/app/mysql-dmysql_datadir=/data/mydata-dwith_ Innobase_storage_engine=1-dwith_archive_stprage_engine=1-dwith_blackhole_storage_engine=1-dwiyh_readline=1- dwiyh_ssl=system-dvith_zlib=system-dwith_lobwrap=0-dmysql_unix_addr=/tmp/mysql.sock-ddefault_charset= Utf8-ddefault_collation=utf8_general_ci

[[email protected] mariadb-10.0.17]# make&& make install

4. Copy the database startup script to the/etc/init.d/mysqld directory and modify the/etc/my.cnf configuration file.

[[Email protected]]# cd/app/mysql/

[Email protected]]# CP support-files/mysql.server/etc/rc.d/init.d/mysqld

[Email protected]]# chmod +x/etc/rc.d/init.d/mysqld

[Email protected]]# CP support-files/my-large.cnf/etc/my.cnf

5. Initialize the database and start the database

[Email protected]]# scripts/mysql_install_db--user=mysql--datadir=/app/mysql/data

[[email protected]] #mkdir log

[[email protected]] #service mysqld start

6. System variables for the database

[Email protected] mysql]# vim/etc/profile.d/mysqld.sh

Export path= $PATH:/usr/local/mysql/bin

[Email protected] mysql]# source/etc/profile.d/mysqld.sh

[[email protected] mysql]# MySQL

Welcome to the MySQL Monitor. Commands End With; or \g.

Your MySQL Connection ID is 148

Server Version:5.5.5-10.0.17-mariadb-logsource Distribution

Copyright (c), And/orits affiliates, Oracle. All rights reserved.

Oracle is a registered trademark of oraclecorporation and/or its

Affiliates. Other names trademarksof their respective

Owners.

Type ' help ', ' or ' \h ' for help. Type ' \c ' to clear the current input statement.

Mysql>

7.master192.168.1.166/etc/my.cnf as follows

[Client]

Port = 3306

Socket =/tmp/mysql.sock

[Mysqld]

Port = 3306

Socket =/tmp/mysql.sock

Skip-external-locking

Key_buffer_size = 256M

Max_allowed_packet = 4690M

Table_open_cache = 16

Sort_buffer_size = 64M

Read_buffer_size = 32M

Read_rnd_buffer_size = 256M

Myisam_sort_buffer_size = 1024M

Thread_cache_size = 8

Query_cache_size= 128M

Log-error=/app/mysql/log/alert.log

Slow_query_log_file=/app/mysql/log/slow.log

General_log_file=/app/mysql/log/general.log

DataDir =/app/mysql/data

Log-bin=mysql-bin

Binlog_format=mixed

Server-id = 1

[Mysqldump]

Quick

Max_allowed_packet = 4690M

[MySQL]

No-auto-rehash

[Myisamchk]

Key_buffer_size = 128M

Sort_buffer_size = 128M

Read_buffer = 2M

Write_buffer = 2M

[Mysqlhotcopy]

Interactive-timeout

8.slave192.168.1.165/etc/my.cnf.

Port = 3306

Socket =/tmp/mysql.sock

Skip-external-locking

Key_buffer_size = 256M

Max_allowed_packet = 1M

Table_open_cache = 256

Sort_buffer_size = 1M

Read_buffer_size = 1M

Read_rnd_buffer_size = 4M

Myisam_sort_buffer_size = 64M

Thread_cache_size = 8

Query_cache_size= 16M

Log-error=/app/mysql/log/alert.log

Slow_query_log_file=/app/mysql/log/slow.log

General_log_file=/app/mysql/log/general.log

Thread_concurrency = 8

DataDir =/data/mydata

Log-bin=mysql-bin

Binlog_format=mixed

Server-id = 2

[Mysqldump]

Quick

Max_allowed_packet = 16M

[MySQL]

No-auto-rehash

[Myisamchk]

Key_buffer_size = 128M

Sort_buffer_size = 128M

Read_buffer = 2M

Write_buffer = 2M

[Mysqlhotcopy]

Interactive-timeout

9. Authorize MySQL on the master database

Mysql> GRANT All privileges on * * to ' root ' @ ' 192.168.1.% identified by ' passwd ' with GRANT OPTION;

Mysql>flush privileges;

Mysql> Show master status;

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

| File | Position | binlog_do_db | binlog_ignore_db |

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

|     mysql-bin.000008 |              2890 |                  | |

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

1 row in Set (0.00 sec)

10. In MySQL from slave

Mysql>stop slave;

mysql> Change Master tomaster_host= ' 192.168.1.166 ', master_user= ' root ', master_password= ' passwd ', master_log_file = ' mysql-bin.000008 ', master_log_pos=2890,master_connect_retry=5,master_heartbeat_period=2,master_port=3306;

Mysql>flush privileges;

Mysql>start slave;

Mysql>show slave Status\g

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

Slave_io_state:waiting Formaster to send event

master_host:192.168.1.166

Master_user:root

master_port:3306

Connect_retry:5

master_log_file:mysql-bin.000008

read_master_log_pos:2890

relay_log_file:zsxyweb3-relay-bin.000002

relay_log_pos:1198

relay_master_log_file:mysql-bin.000008

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

relay_log_space:1498

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

Master_ssl_crlpath:

Using_gtid:no

Gtid_io_pos:

1 row in Set (0.00 sec)

Note: Mainly see if slave_io_running:yes slave_sql_running:yes is Yes

This article from "Underestimate Blog Forum" blog, reproduced please contact the author!

MARIADB Master-Slave synchronization