MariaDB其實和mysql一樣，非有啥不一樣那就是名字不一樣~.~!

開始～上篇實驗是MariaDB的單機(jī)多實例安裝

雙實例端口3307主庫，3308從庫

3307主實例的mysqld配置

[mysqld2]

port=3307

socket=/tmp/mysql3307.sock

pid-file=/tmp/mysql3307.pid

max_allowed_packet=1M

net_buffer_length=2k

table_open_cache=4

sort_buffer_size=64k

thread_stack=128k

basedir=/usr/local/mysql

datadir=/data/mydata2

server-id=244

log-bin = master3307-bin

log-bin-index = master3307-bin.index

thread_concurrency=4

innodb_file_per_table = 1

#mysql_pwd='teleframe'

3308從實例的mysqld配置

[mysqld3]

port=3308

socket=/tmp/mysql3308.sock

pid-file=/tmp/mysql3308.pid

max_allowed_packet=1M

net_buffer_length=2k

table_open_cache=4

sort_buffer_size=64k

thread_stack=128k

basedir=/usr/local/mysql

datadir=/data/mydata3

innodb_file_per_table = 1

server-id=245

log-bin=mysql3308-bin

relay-log = relay-log

relay-log-index = relay-log-index

thread_concurrency=4

read-only = on

開始主從配置

步驟1、連接到主庫上授權(quán)一個REPLICATION SLAVE 帳號，并且刷新授權(quán)表

[root@e3 mysql]# mysql -uroot -h127.0.0.1 -P3307 -p

Enter password:

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

Your MariaDB connection id is 8

Server version: 10.0.15-MariaDB-log Source distribution

Copyright (c) 2000, 2014, Oracle, SkySQL Ab and others.

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

MariaDB [(none)]> show databases;

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

| Database |

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

| information_schema |

| mysql |

| performance_schema |

| test |

| test1 |

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

5 rows in set (0.00 sec)

MariaDB [(none)]> grant replication slave on *.* to 'repl'@'192.168.0.%' identified by 'repl';

Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> flush privileges;

Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> show master status;

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

| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |

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

| master3307-bin.000005 | 632 | | |

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

1 row in set (0.00 sec)

MariaDB [(none)]>

2、連接到從庫上指定主庫，啟動slave

[root@e3 ~]# mysql -uroot -p'teleframe' -h127.0.0.1 -P3308

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

Your MariaDB connection id is 3

Server version: 10.0.15-MariaDB-log Source distribution

Copyright (c) 2000, 2014, Oracle, SkySQL Ab and others.

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

MariaDB [(none)]> change master to master_host='192.168.0.244',master_user='repl',master_password='repl',master_port=3307,master_log_file='master3307-bin.000005',master_log_pos=632;

ERROR 1201 (HY000): Could not initialize master info structure for ''; more error messages can be found in the MariaDB error log #我這報錯了，如果你跟我一樣報錯就執(zhí)行下面的命令

MariaDB [(none)]> reset slave;

Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> change master to master_host='192.168.0.244',master_user='repl',master_password='repl',master_port=3307,;

Query OK, 0 rows affected (0.22 sec) #好了現(xiàn)在連接上了 master_log_file='master3307-bin.000005',master_log_pos=632 表示從主服務(wù)器那個二進(jìn)制日志文件開始復(fù)制因為這是復(fù)制授權(quán)信息，所以沒必要復(fù)制，在主服務(wù)器上可以查看show master status; 查看內(nèi)容show binglog events in 'master-bin.000001';

MariaDB [(none)]> show slave status\G

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

Slave_IO_State:

Master_Host: 192.168.0.244

Master_User: repl

Master_Port: 3307

Connect_Retry: 60

Master_Log_File: master3307-bin.000005

Read_Master_Log_Pos: 632

Relay_Log_File: relay-log.000002

Relay_Log_Pos: 4

Relay_Master_Log_File: master3307-bin.000005

Slave_IO_Running: No # 從服務(wù)器有沒有正常工作主要是看這個值是否YES

Slave_SQL_Running: No #

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

Relay_Log_Space: 248

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

Master_SSL_Crl:

Master_SSL_Crlpath:

Using_Gtid: No

Gtid_IO_Pos:

1 row in set (0.00 sec)

MariaDB [(none)]> start slave; #好啟動slave

Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> show slave status\G

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

Slave_IO_State: Waiting for master to send event

Master_Host: 192.168.0.244

Master_User: repl

Master_Port: 3307

Connect_Retry: 60

Master_Log_File: master3307-bin.000005

Read_Master_Log_Pos: 632

Relay_Log_File: relay-log.000003

Relay_Log_Pos: 540

Relay_Master_Log_File: master3307-bin.000005

Slave_IO_Running: Yes #表示從服務(wù)器正常工作了

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

Relay_Log_Space: 831

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

Master_SSL_Crl:

Master_SSL_Crlpath:

Using_Gtid: No

Gtid_IO_Pos:

來測試！

在主的上面創(chuàng)建一個庫看從的是否可以復(fù)制到

主上創(chuàng)建

MariaDB [(none)]> create database kcwtest;

Query OK, 1 row affected (0.00 sec)

MariaDB [(none)]> show databases;

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

| Database |

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

| information_schema |

| kcwtest |

| mysql |

| performance_schema |

| test |

| test1 |

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

6 rows in set (0.00 sec)

從上查看

MariaDB [(none)]> show databases;

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

| Database |

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

| information_schema |

| kcwtest |

| mysql |

| performance_schema |

| test |

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

5 rows in set (0.00 sec)

可以復(fù)制！這樣就實現(xiàn)單機(jī)實例復(fù)制，我做的3個實例，3306端口給默認(rèn)的用，3307，3308端口做主從復(fù)制。

總結(jié)

以上是生活随笔為你收集整理的linux mysql 单机主从_MariaDB单机双实例主从复制的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯，歡迎將生活随笔推薦給好友。