另一個MySQL DBA: MariaDB的10.0.2檢查

Original post: http://anothermysqldba.blogspot.com/2013/05/checking-out-mariadb-1002.html

我下載了MariaDB的10.0.2源碼包，並做了一個自定義安裝。我這樣做是因為以前的帖子，我有2個已建成的主人。這一次，我刪除的圓形複製他們指出這MariaDB的安裝。我使用的端口3310。剛才投入MariaDB的10.0.2文件夾相同的安裝配置的例子，從以前的帖子會在這裡適用。我在這篇文章的底部增加了安裝，以防萬一，你想要它。

我之所以這樣做是因為我想檢查出的最新MariaDB的功能主要有以下幾種：

多源複製

確保你有不同的IDS服務器設置每台服務器開始。

剛開始，所以沒有在這裡應該預期

> select @@default_master_connection;
+-----------------------------+
| @@default_master_connection |
+-----------------------------+
| |
+-----------------------------+

因此，收集信息，從主服務器

> show master status\G
*************************** 1. row ***************************
File: percona_mysql-bin.000005
Position: 107

現在更新MariaDB的10.0.2從

SET @@default_master_connection='percona';

CHANGE MASTER 'percona' TO MASTER_HOST = '127.0.0.1',
MASTER_USER = 'root',
MASTER_PASSWORD = '',
MASTER_PORT = 3307 ,
MASTER_LOG_FILE = 'percona_mysql-bin.000005',
MASTER_LOG_POS = 107

> select @@default_master_connection;
+-----------------------------+
| @@default_master_connection |
+-----------------------------+
| percona |
+-----------------------------+

OK現在讓我加第二個主

SET @@default_master_connection='oracle';

CHANGE MASTER 'oracle' TO MASTER_HOST = '127.0.0.1',
MASTER_USER = 'root',
MASTER_PASSWORD = '',
MASTER_PORT = 3309 ,
MASTER_LOG_FILE = 'oracle_mysql-bin.000009',
MASTER_LOG_POS = 5453

接下來，您可以檢查狀態，以確保兩個設定都設了。

>SHOW ALL SLAVES STATUS\G

*************************** 1. row ***************************
Connection_name: oracle
Slave_SQL_State:
Slave_IO_State:
Master_Host: 127.0.0.1
Master_User: root
Master_Port: 3309
Connect_Retry: 60
Master_Log_File: oracle_mysql-bin.000009
Read_Master_Log_Pos: 5453
Relay_Log_File: relay-bin-oracle.000001
Relay_Log_Pos: 4
Relay_Master_Log_File: oracle_mysql-bin.000009
Slave_IO_Running: No
Slave_SQL_Running: No
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 5453
Relay_Log_Space: 248
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
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: 0
Retried_transactions: 0
Max_relay_log_size: 1073741824
Executed_log_entries: 0
Slave_received_heartbeats: 0
Slave_heartbeat_period: 1800.000
Gtid_Pos:
*************************** 2. row ***************************
Connection_name: percona
Slave_SQL_State:
Slave_IO_State:
Master_Host: 127.0.0.1
Master_User: root
Master_Port: 3307
Connect_Retry: 60
Master_Log_File: percona_mysql-bin.000005
Read_Master_Log_Pos: 107
Relay_Log_File: relay-bin-percona.000001
Relay_Log_Pos: 4
Relay_Master_Log_File: percona_mysql-bin.000005
Slave_IO_Running: No
Slave_SQL_Running: No
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 107
Relay_Log_Space: 248
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
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: 0
Retried_transactions: 0
Max_relay_log_size: 1073741824
Executed_log_entries: 0
Slave_received_heartbeats: 0
Slave_heartbeat_period: 1800.000
Gtid_Pos:

確定的時間開始

> START ALL SLAVES;
Query OK, 0 rows affected, 2 warnings (0.00 sec)

root@localhost [(none)]> show warnings;
+-------+------+-------------------------+
| Level | Code | Message |
+-------+------+-------------------------+
| Note | 1937 | SLAVE 'percona' started |
| Note | 1937 | SLAVE 'oracle' started |
+-------+------+-------------------------+

Relay_Master_Log_File: percona_mysql-bin.000005
Slave_IO_Running: Yes
Slave_SQL_Running: Yes

Relay_Master_Log_File: oracle_mysql-bin.000009

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

因此，讓我們測試一些情況。

通過Percona的主

use test;
CREATE TABLE `multi_test` (
`time_recorded` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB;

MariaDB的奴隸

> show tables;
+----------------+
| Tables_in_test |
+----------------+
| multi_test |
+----------------+

通過Oracle MySQL主

use test;
CREATE TABLE `multi_test2` (
`time_recorded` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB;

MariaDB的奴隸

> show tables;
+----------------+
| Tables_in_test |
+----------------+
| multi_test |
| multi_test2 |
+----------------+

確定工作！

SHOW解釋
這是相當直線前進，但因為它是運行很高興趕上查詢，。

> show explain for 17;
+------+-------------+--------+-------+---------------+---------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+--------+-------+---------------+---------+---------+------+------+-------------+
| 1 | SIMPLE | sbtest | range | PRIMARY | PRIMARY | 4 | NULL | 99 | Using where |
+------+-------------+--------+-------+---------------+---------+---------+------+------+-------------+
1 row in set, 1 warning (0.00 sec)

root@localhost [test]> show warnings;
+-------+------+----------------------------------------------------------+
| Level | Code | Message |
+-------+------+----------------------------------------------------------+
| Note | 1003 | SELECT SUM(K) from sbtest where id between 4997 and 5096 |
+-------+------+----------------------------------------------------------+

便箋：

Cassandra的存儲引擎
我很好奇，關於這一點，它涉及如何通過memcache的NoSQL和Innodb的解決方案。
我有一個後，在這裡： http://anothermysqldba.blogspot.com/2013/04/nosql-php-memcache-innodb-mysql.html

我會回來的這一點，因為我在我的環境中成立卡桑德拉。我並不渴望，但好奇。

用戶反饋插件
文檔“快速啟動”添加到下的my.cnf文件的[mysqld]

[mysqld]
feedback=ON
port = 3310
socket = /tmp/mariadb-10.0.2.sock

130513 17:45:10 InnoDB: 10.0.2-MariaDB started; log sequence number 20183690
130513 17:45:10 [ERROR] /usr/local/mariadb-10.0.2/bin/mysqld: unknown variable 'feedback=ON'

這個工作輕鬆了很多，想到這樣一旦我刪除了“快速啟動”的指示。

> INSTALL PLUGIN feedback SONAME 'feedback.so';

> SELECT plugin_status FROM information_schema.plugins WHERE plugin_name = 'feedback';
+---------------+
| plugin_status |
+---------------+
| ACTIVE |
+---------------+

通過錯誤日誌中，你也可以看到它的工作：

[注意事項]反饋插件：發送報告'http://mariadb.org/feedback_plugin/post'
[注]反饋插件：服務器回答說'OK'

總體而言，我目前最喜歡的增強：

基本安裝是這樣的：

# Preconfiguration setup
shell> groupadd mariadb
shell> useradd -r -g mariadb mariadb

# Beginning of source-build specific instructions
shell> tar zxvf MariaDB-VERSION.tar.gz
shell> cd MariaDB-VERSION
shell> cmake .
shell> make
shell> make install DESTDIR="/usr/local/mariadb-10.0.2-tmp"
# End of source-build specific instructions

Build files have been written to: /usr/local/src/MySQL/MariaDB/10.0.2/mariadb-10.0.2

I do not like the results
-- Installing: /usr/local/mariadb-10.0.2-tmp/usr/local/mysql/
If DESTDIR is should install into that location not start with user under that location. This is a MySQL original issue as it does this with all versions of MySQL.

# Fix the odd/bug setup
shell> cd /usr/local/mariadb-10.0.2-tmp
shell> mv usr/local/mysql/ ../mariadb-10.0.2 ;
shell> cd ../; # rm -Rf mariadb-10.0.2-tmp

# Postinstallation setup
shell> cd /usr/local/mariadb-10.0.2
shell> chown -R mariadb .
shell> chgrp -R mariadb .

# Next command is optional
shell> cp support-files/my-small.cnf /etc/mariadb-10.0.2.cnf
shell> vi /etc/mariadb-10.0.2.cnf
port = 3310
socket = /tmp/mariadb-10.0.2.sock

shell> scripts/mysql_install_db --defaults-file=/etc/mariadb-10.0.2.cnf --basedir=/usr/local/mariadb-10.0.2 --skip-name-resolve --datadir=/var/lib/mariadb-10.0.2 --user=mariadb
shell> chown -R mariadb /var/lib/mariadb-10.0.2/*

shell> # bin/mysqld_safe --defaults-file=/etc/mariadb-10.0.2.cnf --user=mariadb --datadir=/var/lib/mariadb-10.0.2/ --port=3310 &


shell> # ./bin/mysql --port=3310 --socket=/tmp/mariadb-10.0.2.sock
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 2
Server version: 10.0.2-MariaDB Source distribution

Copyright (c) 2000, 2013, Oracle, Monty Program Ab and others.

另一個MySQL DBA

Pages

2013年5月14日星期二

MariaDB的10.0.2檢查