amir jj's questions

我已经安装了 MySQL Cluster 必要的包，我现在正在使用自动安装程序正确配置节点。当我尝试启动 Cluster In Deploy Configurationpart 时，除 SQL 节点外，所有节点（1 个管理节点、4 个数据节点）都正确启动，并且出现以下错误。

Command `/usr/local/mysql/bin/mysqld --defaults-file=/home/debian/MySQL_Cluster/49/my.cnf', running on 192.168.120.107 exited with 1:
2016-04-03 16:32:49 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2016-04-03 16:32:49 0 [Note] /usr/local/mysql/bin/mysqld (mysqld 5.6.28-ndb-7.4.10-cluster-gpl) starting as process 11472 ..

您会看到所有节点都已启动。我使用它推荐的命令--explicit_defaults_for_timestamp和如下选项。

 /usr/local/mysql/bin/mysqld --defaults-file=/home/debian/MySQL_Cluster/49/my.cnf  --explicit_defaults_for_timestamp 
2016-04-03 16:42:06 0 [Note] /usr/local/mysql/bin/mysqld (mysqld 5.6.28-ndb-7.4.10-cluster-gpl) starting as process 11503 ...

您可以看到--explicit_defaults_for_timestamp 选项服务正确启动，但是当我使用管理节点查看状态时，我仍然看到集群有问题，请参见管理节点：

ndb_mgm> show 
Connected to Management Server at: localhost:1186
Cluster Configuration
---------------------
[ndbd(NDB)] 4 node(s)
id=1    @192.168.120.111  (mysql-5.6.28 ndb-7.4.10, Nodegroup: 0, *)
id=2    @192.168.120.117  (mysql-5.6.28 ndb-7.4.10, Nodegroup: 0)
id=3    @192.168.120.118  (mysql-5.6.28 ndb-7.4.10, Nodegroup: 1)
id=4    @192.168.120.76  (mysql-5.6.28 ndb-7.4.10, Nodegroup: 1)

[ndb_mgmd(MGM)] 1 node(s)
id=50   @192.168.120.79  (mysql-5.6.28 ndb-7.4.10)

[mysqld(API)]   1 node(s)
id=49 (not connected, accepting connect from 192.168.120.107)

如您所见，SQL 节点仍未正确启动：

[mysqld(API)]   1 node(s)
id=49 (not connected, accepting connect from 192.168.120.107)

我不明白这个问题。

我有一个 MySQL 双主复制 MySQL，每台服务器都有 21 GB 内存，我现在正在为这个项目寻找集群解决方案，可能有 6 个服务器：其中两个有 20 GB RAM 和其他 10 GB RAM。在 MySQL Cluster 文档中，它为总内存和每个节点 RAM 编写了以下公式，看来我需要更多现有 Replication 的 RAM，并且很难让我的公司同意这种 RAM 要求，除了目的集群不是让大型服务器使用相互连接的较小（资源）服务器，但使用此公式，集群的每个节点都比我现有的服务器大得多。

如果设计一个全新的数据库，可以使用以下计算来帮助确定数据节点的大致内存大小要求：

•（在内存中）数据大小 * 副本 * 1.25 = 总数据库内存要求示例：50 GB * 2 * 1.25 = 125 GB

• (数据大小 * 副本 * 1.25)/节点 = 每个节点的 RAM 示例：(2 GB * 2 * 1.25)/4 = 31.25 GB

在这种情况下，我有一些问题：

• 有必要遵守这些公式吗？我可以为节点使用更少的 RAM 吗？

我有两个数据库，一个表，一个utf8表，我想更改列的编码，我使用了两个场景，但一个问题发生在我身上。起初桌子是这样的：

CREATE TABLE `spool` (
  `username` varchar(250) NOT NULL,
  `xml` text  NOT NULL,
  `seq` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY `seq` (`seq`),
  KEY `i_despool` (`username`) USING BTREE,
  KEY `i_spool_created_at` (`created_at`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8

第一种情况

在第一个中，我使用以下命令更改行编码：

ALTER TABLE spool MODIFY xml TEXT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL;

然后我show create table spool;像这样使用：

 CREATE TABLE `spool` (
  `username` varchar(250) NOT NULL,
  `xml` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `seq` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY `seq` (`seq`),
  KEY `i_despool` (`username`) USING BTREE,
  KEY `i_spool_created_at` (`created_at`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8

第二种情况

在第二个表中，我首先更改了表编码，然后更改了xml列编码，如下所示：

ALTER TABLE spool 
CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

然后我更改了列编码，如下所示：

ALTER TABLE spool MODIFY xml TEXT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL;

然后我们可以看到这样的表格：

CREATE TABLE `spool` (
  `username` varchar(250) COLLATE utf8mb4_unicode_ci NOT NULL,
  `xml` text COLLATE utf8mb4_unicode_ci NOT NULL,
  `seq` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY `seq` (`seq`),
  KEY `i_despool` (`username`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=30849368 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

正如您在“`xml`文本”前面的第二种情况中看到的那样，我们看不到CHARACTER SET utf8mb4，我认为这对我来说会导致错误，因为当 Ejabberd 想要在第二个中插入查询时，我看到以下错误但在另一个表我没有这个错误：

HY000Incorrect string value: '\\xF0\\x9F\\x98\\x8F\\xF0\\x9F...' for column 'xml' at row 1"
** Stacktrace: [{ejabberd_odbc,sql_query_t,1,[{file,"src/ejabberd_odbc.erl"},{line,173}]},{lists,foreach,2,[{file,"lists.erl"},{line,1336}]},{ejabberd_odbc,outer_transaction,3,[{file,"src/ejabberd_odbc.erl"},{line,443}]},{ejabberd_odbc,run_sql_cmd,4,[{file,"src/ejabberd_odbc.erl"},{line,380}]},{p1_fsm,handle_msg,10,[{file,"src/p1_fsm.erl"},{line,582}]},{proc_lib,init_p_do_apply,3,[{file,"proc_lib.erl"},{line,237}]}]

在第一种情况下，我们看不到此错误，但在第二种情况下存在错误。如何解决这个问题呢？

我有三台服务器，我想在它们上安装 Galera 集群，我按照这个链接安装和配置它。但是当我想启动第一台服务器时，我收到了这个错误：

root@node107:/home/debian# service mysql start --wsrep-new-clusterping 
Job for mysql.service failed. See 'systemctl status mysql.service' and 'journalctl -xn' for details.

和 ：

root@node107:/home/debian# journalctl -xn
-- Logs begin at Tue 2016-03-01 19:02:04 IRST, end at Tue 2016-03-01 19:39:32 IRST. --
Mar 01 19:39:31 node107 mysqld[5102]: 160301 19:39:31 [ERROR] WSREP: gcs/src/gcs.cpp:gcs_open():1379: Failed to open channel 'test_cluster' at 'gcomm://192.168.120.107,192.168.120.111,192.168.120.11
Mar 01 19:39:31 node107 mysqld[5102]: 160301 19:39:31 [ERROR] WSREP: gcs connect failed: Connection timed out
Mar 01 19:39:31 node107 mysqld[5102]: 160301 19:39:31 [ERROR] WSREP: wsrep::connect(gcomm://192.168.120.107,192.168.120.111,192.168.120.117) failed: 7
Mar 01 19:39:31 node107 mysqld[5102]: 160301 19:39:31 [ERROR] Aborting
Mar 01 19:39:31 node107 mysqld[5102]: 
Mar 01 19:39:31 node107 mysqld[5102]: 160301 19:39:31 [Note] WSREP: Service disconnected.
Mar 01 19:39:32 node107 mysqld[5102]: 160301 19:39:32 [Note] WSREP: Some threads may fail to exit.
Mar 01 19:39:32 node107 mysqld[5102]: 160301 19:39:32 [Note] /usr/sbin/mysqld: Shutdown complete
Mar 01 19:39:32 node107 mysqld[5102]: 
Mar 01 19:39:32 node107 mysqld_safe[5378]: mysqld from pid file /var/run/mysqld/mysqld.pid ended

这些是三台服务器的配置文件：

vim /etc/mysql/conf.d/cluster.cnf

服务器1：

[mysqld]
query_cache_size=0
binlog_format=ROW
default-storage-engine=innodb
innodb_autoinc_lock_mode=2
query_cache_type=0
bind-address=0.0.0.0

# Galera Provider Configuration
wsrep_provider=/usr/lib/galera/libgalera_smm.so
#wsrep_provider_options="gcache.size=32G"

# Galera Cluster Configuration
wsrep_cluster_name="test_cluster"
wsrep_cluster_address="gcomm://192.168.120.107,192.168.120.111,192.168.120.117"
#wsrep_cluster_address="gcomm://192.168.120.107"

# Galera Synchronization Congifuration
wsrep_sst_method=rsync
#wsrep_sst_auth=user:pass

# Galera Node Configuration
wsrep_node_address="192.168.120.107"
wsrep_node_name="node107"

服务器 2：

[mysqld]
query_cache_size=0
binlog_format=ROW
default-storage-engine=innodb
innodb_autoinc_lock_mode=2
query_cache_type=0
bind-address=0.0.0.0

# Galera Provider Configuration
wsrep_provider=/usr/lib/galera/libgalera_smm.so
#wsrep_provider_options="gcache.size=32G"

# Galera Cluster Configuration
wsrep_cluster_name="test_cluster"
wsrep_cluster_address="gcomm://192.168.120.107"
#wsrep_cluster_address="gcomm://192.168.120.107,192.168.120.111,192.168.120.117"

# Galera Synchronization Congifuration
wsrep_sst_method=rsync
#wsrep_sst_auth=user:pass

# Galera Node Configuration
wsrep_node_address="192.168.120.111"
wsrep_node_name="node111"

服务器 3：

[mysqld]
query_cache_size=0
binlog_format=ROW
default-storage-engine=innodb
innodb_autoinc_lock_mode=2
query_cache_type=0
bind-address=0.0.0.0

# Galera Provider Configuration
wsrep_provider=/usr/lib/galera/libgalera_smm.so
#wsrep_provider_options="gcache.size=32G"

# Galera Cluster Configuration
wsrep_cluster_name="test_cluster"
wsrep_cluster_address="gcomm://192.168.120.107,192.168.120.111,192.168.120.117"

# Galera Synchronization Congifuration
wsrep_sst_method=rsync
#wsrep_sst_auth=user:pass

# Galera Node Configuration
wsrep_node_address="192.168.120.117"
wsrep_node_name="node117"

我不明白我错过了什么。我应该怎么办？

我有一个带有字符集的数据库latin1，我需要将表字符集更改为utf8，但出现错误。当前字符集是：

mysql> show variables like "%character%";
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | utf8                       |
| character_set_connection | utf8                       |
| character_set_database   | latin1                     |
| character_set_filesystem | binary                     |
| character_set_results    | utf8                       |
| character_set_server     | latin1                     |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+

我要更改字符集的表是：

mysql> describe spool;
+------------+---------------------+------+-----+-------------------+----------------+
| Field      | Type                | Null | Key | Default           | Extra          |
+------------+---------------------+------+-----+-------------------+----------------+
| username   | varchar(250)        | NO   | MUL | NULL              |                |
| xml        | text                | NO   |     | NULL              |                |
| seq        | bigint(20) unsigned | NO   | PRI | NULL              | auto_increment |
| created_at | timestamp           | NO   |     | CURRENT_TIMESTAMP |                |
+------------+---------------------+------+-----+-------------------+----------------+
4 rows in set (0.00 sec)

当我想更改字符集时，我看到以下错误：

mysql> ALTER TABLE spool CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ERROR 1071 (42000): Specified key was too long; max key length is 767 bytes

这是一个公司运作的数据库，我不想打扰功能，所以我不想改变varchar(250)类型的大小，我想知道是否有解决方案来改变这种编码。

mysql> show create table spool\G;
*************************** 1. row ***************************
       Table: spool
Create Table: CREATE TABLE `spool` (
  `username` varchar(250) NOT NULL,
  `xml` text NOT NULL,
  `seq` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY `seq` (`seq`),
  KEY `i_despool` (`username`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=23490775 DEFAULT CHARSET=utf8

我有 MySQL 服务器 5.6，我使用压力测试来评估它的性能。测试后的结果mysqltuner.pl如下：

root@master-1:/home/debian# perl mysqltuner.pl
Please enter your MySQL administrative login: root
Please enter your MySQL administrative password:  >>  MySQLTuner 1.6.4 - Major Hayden <[email protected]>
 >>  Bug reports, feature requests, and downloads at http://mysqltuner.com/
 >>  Run with '--help' for additional options and output filtering
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.6.29-log
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------
[--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MRG_MYISAM 
[--] Data in InnoDB tables: 34G (Tables: 29)
[!!] Total fragmented tables: 4

-------- Security Recommendations  -------------------------------------------
[OK] There are no anonymous accounts for any database users
[OK] All database users have passwords assigned
[!!] There is no basic password file list!

-------- CVE Security Recommendations  ---------------------------------------
[--] Skipped due to --cvefile option undefined

-------- Performance Metrics -------------------------------------------------
[--] Up for: 1h 56m 26s (4M q [592.634 qps], 398 conn, TX: 8B, RX: 6B)
[--] Reads / Writes: 81% / 19%
[--] Binary logging is enabled (GTID MODE: OFF)
[--] Total buffers: 1.0G global + 1.1M per thread (8190 max threads)
[OK] Maximum reached memory usage: 1.1G (4.63% of installed RAM)
[OK] Maximum possible memory usage: 9.5G (40.45% of installed RAM)
[OK] Slow queries: 0% (0/4M)
[OK] Highest usage of available connections: 0% (44/8190)
[OK] Aborted connections: 0.75%  (3/398)
[!!] Query cache is disabled
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 2 sorts)
[OK] Temporary tables created on disk: 0% (0 on disk / 27 total)
[OK] Thread cache hit rate: 84% (62 created / 398 connections)
[OK] Table cache hit rate: 75% (309 open / 408 opened)
[OK] Open file limit used: 0% (54/9K)
[OK] Table locks acquired immediately: 100% (4M immediate / 4M locks)
[OK] Binlog cache memory access: 100.00% ( 704225 Memory / 704225 Total)

-------- MyISAM Metrics ------------------------------------------------------
[!!] Key buffer used: 18.2% (3M used / 16M cache)
[OK] Key buffer size / total MyISAM indexes: 16.0M/95.0K
[!!] Read Key buffer hit rate: 80.0% (5 cached / 1 reads)

-------- InnoDB Metrics ------------------------------------------------------
[--] InnoDB is enabled.
[!!] InnoDB buffer pool / data size: 512.0M/34.2G
[!!] InnoDB buffer pool <= 1G and innodb_buffer_pool_instances(!=1).
[OK] InnoDB Used buffer: 96.95% (31769 used/ 32767 total)
[OK] InnoDB Read buffer efficiency: 99.83% (94182005 hits/ 94340114 total)
[!!] InnoDB Write buffer efficiency: 0.00% (0 hits/ 1 total)
[OK] InnoDB log waits: 0.00% (0 waits / 178758 writes)

-------- ThreadPool Metrics --------------------------------------------------
[--] ThreadPool stat is disabled.

-------- AriaDB Metrics ------------------------------------------------------
[--] AriaDB is disabled.

-------- TokuDB Metrics ------------------------------------------------------
[--] TokuDB is disabled.

-------- Galera Metrics ------------------------------------------------------
[--] Galera is disabled.

-------- Replication Metrics -------------------------------------------------
[--] This server is acting as master for 2 server(s).
[!!] This replication slave is running with the read_only option disabled.
[OK] This replication slave is up to date with master.

-------- Recommendations -----------------------------------------------------

然后我停止压力测试脚本，我发现内存几乎已满，MySQL 不会释放它（即使在一天没有任何查询并重新启动 MySQL 之后），类似于内存泄漏或错误。在my.cnf缓冲区大小中，innodb_buffer_pool_size=512M并且在将其更改为之后，2G但没有发生任何有用的事情。在本教程中，我使用了以下命令，但我看到了一些不好的东西：

询问：

select
page_type as Page_Type,
sum(data_size)/1024/1024 as Size_in_MB
from information_schema.innodb_buffer_page
group by page_type
order by Size_in_MB desc;

结果 ：

+--------------------------------+------------+------------+------------+
| Table_Name                     | Index_Name | Page_Count | Size_in_MB |
+--------------------------------+------------+------------+------------+
| `SYS_COLUMNS`                  | CLUST_IND  |          1 | 0.01321316 |
| `SYS_INDEXES`                  | CLUST_IND  |          1 | 0.00615215 |
| `SYS_FIELDS`                   | CLUST_IND  |          1 | 0.00364017 |
| `SYS_TABLES`                   | CLUST_IND  |          1 | 0.00293446 |
| `SYS_FOREIGN`                  | FOR_IND    |          1 | 0.00023651 |
| `SYS_FOREIGN`                  | REF_IND    |          1 | 0.00022316 |
| NULL                           | NULL       |     131055 | 0.00000000 |
| `mysql`.`slave_relay_log_info` | PRIMARY    |          1 | 0.00000000 |
| `mysql`.`slave_master_info`    | PRIMARY    |          1 | 0.00000000 |
| `mysql`.`slave_worker_info`    | PRIMARY    |          1 | 0.00000000 |
+--------------------------------+------------+------------+------------+
10 rows in set (1.49 sec)

如您所见，一行是NULL并且有131055page_count。缓冲池中页面类型的结果如下：

询问：

select
page_type as Page_Type,
sum(data_size)/1024/1024 as Size_in_MB
from information_schema.innodb_buffer_page
group by page_type
order by Size_in_MB desc;

结果：

+-------------------+------------+
| Page_Type         | Size_in_MB |
+-------------------+------------+
| INDEX             | 0.02639961 |
| IBUF_INDEX        | 0.00000000 |
| INODE             | 0.00000000 |
| IBUF_BITMAP       | 0.00000000 |
| SYSTEM            | 0.00000000 |
| UNKNOWN           | 0.00000000 |
| FILE_SPACE_HEADER | 0.00000000 |
| UNDO_LOG          | 0.00000000 |
| TRX_SYSTEM        | 0.00000000 |
+-------------------+------------+
9 rows in set (1.04 sec)

结果htop如下：

当 MySQL 服务器上没有任何负载时，您可以查看缓存大小使用情况和mysqld进程。

我不明白为什么没有释放内存（缓存），或者我想知道这是否是一个错误。

所以我创建了一个多主复制，其中两个用于主服务器，两个用于从服务器。

主机名：master-1 ip：192.168.120.107

主机名：master-2 ip：192.168.120.111

主机名：slave-1 ip：192.168.120.117

主机名：slave-2 ip：192.168.120.118

两个主服务器（master-1，master-2）被部署为主动 - 主动复制并且运行良好，下面是配置文件/etc/mysql/my.cnf：

大师-1：

[mysqld]
#
# * Basic Settings
#
server-id       = 1
user            = root
pid-file        = /var/run/mysqld/mysqld.pid
socket          = /var/run/mysqld/mysqld.sock
port            = 3306
basedir         = /usr
datadir         = /var/lib/mysql
tmpdir          = /tmp
lc-messages-dir = /usr/share/mysql
log-bin         = master-bin
log-bin-index   = master-bin.index
relay-log       = slave-relay-bin
relay-log-index = slave-relay-bin.index
skip-external-locking

大师-2：

[mysqld]
#
# * Basic Settings
#
server-id       = 2
user            = root
pid-file        = /var/run/mysqld/mysqld.pid
socket          = /var/run/mysqld/mysqld.sock
port            = 3306
basedir         = /usr
datadir         = /var/lib/mysql
tmpdir          = /tmp
lc-messages-dir = /usr/share/mysql
log-bin         = master-bin
log-bin-index   = master-bin.index
relay-log       = relay-log-bin
relay-log-index = relay-log-bin.index
skip-external-locking

大师们工作得很好。两个从站像这样连接到主站： slave-1 是 master-1 的从站，slave-2 是 master-2 的从站。这些是配置文件：

从属 1：

[mysqld]
#
# * Basic Settings
#
server-id       = 3
user            = mysql
pid-file        = /var/run/mysqld/mysqld.pid
socket          = /var/run/mysqld/mysqld.sock
port            = 3306
basedir         = /usr
datadir         = /var/lib/mysql
tmpdir          = /tmp
lc-messages-dir = /usr/share/mysql
relay-log       = slave-relay-bin
relay-log-index = slave-relay-bin.index
skip-external-locking

从属 2：

[mysqld]
#
# * Basic Settings
#
server-id       = 4
user            = mysql
pid-file        = /var/run/mysqld/mysqld.pid
socket          = /var/run/mysqld/mysqld.sock
port            = 3306
basedir         = /usr
datadir         = /var/lib/mysql
tmpdir          = /tmp
lc-messages-dir = /usr/share/mysql
relay-log       = slave-relay-bin
relay-log-index = slave-relay-bin.index
skip-external-locking

当主服务器对数据库进行更改时，他的从服务器更改了两个，这意味着工作正常，例如当 master-1 更改数据库时，更改将被复制到 master-2 和 slave-1，并且也会在这些数据库中完成更改。我的问题是我需要在所有从机中发生更改，例如当 master-1 更改数据库时，slave-2 更改他的数据库，如 master-2 和 slave-1。我怎样才能做到这一点？