nelaaro's questions

我有下表。

我也想添加分区。

CREATE TABLE `app_log_Test` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `dateCreated` datetime NOT NULL,
  `host` varchar(512) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `label` varchar(32) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `event` varchar(32) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `level` varchar(8) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `message` text COLLATE utf8mb4_unicode_ci,
  `version` bigint(20) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `app_log_dateCreated` (`dateCreated`),
  KEY `app_log_label` (`label`),
  KEY `app_log_event` (`event`),
  KEY `app_log_level` (`level`)
) ENGINE=TokuDB `COMPRESSION`=tokudb_quicklz

我正在使用 MariaDB 10。

MariaDB [test2]> alter table app_log_Test partition by RANGE(TO_DAYS(dateCreated))(
-> PARTITION p_201809 VALUES LESS THAN (TO_DAYS('2018-09-01 00:00:00')) ENGINE = TokuDB,
-> PARTITION p_201810 VALUES LESS THAN (TO_DAYS('2018-10-01 00:00:00')) ENGINE = TokuDB);

我收到以下错误

ERROR 1503 (HY000): A PRIMARY KEY must include all columns in the table's partitioning function

有没有办法让 tokudb 将它为数据库创建的所有文件放入子目录中。

当我开始使用 tokudb 时，我发现最烦人的事情是它将所有文件放入 MySQL 根数据库目录中。

这使得很难看出哪些文件属于哪个数据库，也很难看出每个数据库模式使用了多少空间。

ll /srv/mysql/data/

-rw-rw----. 1 mysql mysql    16384 Apr  5 17:36 aria_log.00000001
-rw-rw----. 1 mysql mysql       52 Apr  5 17:36 aria_log_control
drwx------. 2 mysql mysql     4096 Apr 10 15:09 graphite
drwx------. 2 mysql mysql      165 Apr 10 15:09 graphite2
-rw-rw----. 1 mysql mysql    32768 Apr  6 11:58 _graphite_account_mygraph_key_account_mygraph_83a0eb3f_3c3_3_1d.tokudb
-rw-rw----. 1 mysql mysql    32768 Apr  6 11:58 _graphite_account_mygraph_main_3c3_2_1d.tokudb
-rw-rw----. 1 mysql mysql    65536 Apr 10 14:54 _graphite_account_mygraph_status_3c3_1_1d.tokudb
-rw-rw----. 1 mysql mysql    32768 Apr  6 11:58 _graphite_account_profile_key_user_id_3ce_3_1d.tokudb
-rw-rw----. 1 mysql mysql    32768 Apr  6 11:58 _graphite_account_profile_main_3ce_2_1d.tokudb
-rw-rw----. 1 mysql mysql    65536 Apr  6 15:54 _graphite_account_profile_status_3ce_1_1d.tokudb
-rw-rw----. 1 mysql mysql    32768 Apr  6 11:58 _graphite_account_variable_key_account_variable_83a0eb3f_3db_3_1d.tokudb
-rw-rw----. 1 mysql mysql    32768 Apr  6 11:58 _graphite_account_variable_main_3db_2_1d.tokudb
-rw-rw----. 1 mysql mysql    65536 Apr 10 14:54 _graphite_account_variable_status_3db_1_1d.tokudb
-rw-rw----. 1 mysql mysql    32768 Apr  6 11:58 _graphite_account_view_key_account_view_83a0eb3f_3e6_3_1d.tokudb
-rw-rw----. 1 mysql mysql    32768 Apr  6 11:58 _graphite_account_view_main_3e6_2_1d.tokudb
-rw-rw----. 1 mysql mysql    65536 Apr 10 14:54 _graphite_account_view_status_3e6_1_1d.tokudb
-rw-rw----. 1 mysql mysql    32768 Apr  6 11:58 _graphite_account_window_key_account_window_2e1a1398_3f1_3_1d.tokudb
-rw-rw----. 1 mysql mysql    32768 Apr  6 11:58 _graphite_account_window_main_3f1_2_1d.tokudb
-rw-rw----. 1 mysql mysql    65536 Apr 10 14:54 _graphite_account_window_status_3f1_1_1d.tokudb
-rw-rw----. 1 mysql mysql    32768 Apr  6 11:58 _graphite_auth_group_key_name_3fc_3_1d.tokudb
-rw-rw----. 1 mysql mysql    32768 Apr  6 11:58 _graphite_auth_group_main_3fc_2_1d.tokudb
-rw-rw----. 1 mysql mysql    32768 Apr  6 11:58 _graphite_auth_group_permissions_key_auth_group_permissions_0e939a4f_407_4_1d.tokudb
-rw-rw----. 1 mysql mysql    32768 Apr  6 11:58 _graphite_auth_group_permissions_key_auth_group_permissions_8373b171_407_5_1d.tokudb
-rw-rw----. 1 mysql mysql    32768 Apr  6 11:58 _graphite_auth_group_permissions_key_auth_group_permissions_group_id_0cd325b0_uniq_407_3_1d.tokudb
-rw-rw----. 1 mysql mysql    32768 Apr  6 11:58 _graphite_auth_group_permissions_main_407_2_1d.tokudb
-rw-rw----. 1 mysql mysql    65536 Apr 10 14:54 _graphite_auth_group_permissions_status_407_1_1d.tokudb
-rw-rw----. 1 mysql mysql    65536 Apr 10 14:54 _graphite_auth_group_status_3fc_1_1d.tokudb
-rw-rw----. 1 mysql mysql    16896 Apr  6 11:58 _graphite_auth_permission_key_auth_permission_417f1b1c_419_1_1d_B_2.tokudb
-rw-rw----. 1 mysql mysql    16896 Apr  6 11:58 _graphite_auth_permission_key_auth_permission_content_type_id_01ab375a_uniq_419_1_1d_B_1.tokudb
-rw-rw----. 1 mysql mysql    16896 Apr  6 11:58 _graphite_auth_permission_main_419_1_1d_B_0.tokudb
-rw-rw----. 1 mysql mysql    65536 Apr 10 14:54 _graphite_auth_permission_status_412_1_1d.tokudb
-rw-rw----. 1 mysql mysql    32768 Apr  6 11:58 _graphite_auth_user_groups_key_auth_user_groups_0e939a4f_42c_5_1d.tokudb
-rw-rw----. 1 mysql mysql    32768 Apr  6 11:58 _graphite_auth_user_groups_key_auth_user_groups_e8701ad4_42c_4_1d.tokudb
-rw-rw----. 1 mysql mysql    32768 Apr  6 11:58 _graphite_auth_user_groups_key_auth_user_groups_user_id_94350c0c_uniq_42c_3_1d.tokudb
-rw-rw----. 1 mysql mysql    32768 Apr  6 11:58 _graphite_auth_user_groups_main_42c_2_1d.tokudb
-rw-rw----. 1 mysql mysql    65536 Apr 10 14:54 _graphite_auth_user_groups_status_42c_1_1d.tokudb
-rw-rw----. 1 mysql mysql    32768 Apr  6 11:58 _graphite_auth_user_key_username_41f_3_1d.tokudb
-rw-rw----. 1 mysql mysql    32768 Apr  6 11:58 _graphite_auth_user_main_41f_2_1d.tokudb
-rw-rw----. 1 mysql mysql    65536 Apr  6 15:54 _graphite_auth_user_status_41f_1_1d.tokudb
-rw-rw----. 1 mysql mysql    32768 Apr  6 11:58 _graphite_auth_user_user_permissions_key_auth_user_user_permissions_8373b171_437_5_1d.tokudb
-rw-rw----. 1 mysql mysql    32768 Apr  6 11:58 _graphite_auth_user_user_permissions_key_auth_user_user_permissions_e8701ad4_437_4_1d.tokudb
-rw-rw----. 1 mysql mysql    32768 Apr  6 11:58 _graphite_auth_user_user_permissions_key_auth_user_user_permissions_user_id_14a6b632_uniq_437_3_1d.tokudb
-rw-rw----. 1 mysql mysql    32768 Apr  6 11:58 _graphite_auth_user_user_permissions_main_437_2_1d.tokudb
-rw-rw----. 1 mysql mysql    65536 Apr 10 14:54 _graphite_auth_user_user_permissions_status_437_1_1d.tokudb
-rw-rw----. 1 mysql mysql    32768 Apr  6 11:58 _graphite_dashboard_dashboard_main_442_2_1d.tokudb
-rw-rw----. 1 mysql mysql    32768 Apr  6 11:58 _graphite_dashboard_dashboard_owners_key_dashboard_dashboard_owners_83a0eb3f_44c_5_1d.tokudb
-rw-rw----. 1 mysql mysql    32768 Apr  6 11:58 _graphite_dashboard_dashboard_owners_key_dashboard_dashboard_owners_a6b0b808_44c_4_1d.tokudb
-rw-rw----. 1 mysql mysql    32768 Apr  6 11:58 _graphite_dashboard_dashboard_owners_key_dashboard_dashboard_owners_dashboard_id_f37e04b7_uniq_44c_3_1d.tokudb
-rw-rw----. 1 mysql mysql    32768 Apr  6 11:58 _graphite_dashboard_dashboard_owners_main_44c_2_1d.tokudb
-rw-rw----. 1 mysql mysql    65536 Apr 10 14:54 _graphite_dashboard_dashboard_owners_status_44c_1_1d.tokudb
-rw-rw----. 1 mysql mysql    16384 Apr  6 11:58 _graphite_dashboard_dashboard_status_442_1_1d.tokudb
-rw-rw----. 1 mysql mysql    32768 Apr  6 11:58 _graphite_dashboard_template_main_457_2_1d.tokudb
-rw-rw----. 1 mysql mysql    32768 Apr  6 11:58 _graphite_dashboard_template_owners_key_dashboard_template_owners_74f53564_461_4_1d.tokudb
-rw-rw----. 1 mysql mysql    32768 Apr  6 11:58 _graphite_dashboard_template_owners_key_dashboard_template_owners_83a0eb3f_461_5_1d.tokudb
-rw-rw----. 1 mysql mysql    32768 Apr  6 11:58 _graphite_dashboard_template_owners_key_dashboard_template_owners_template_id_e47a75a7_uniq_461_3_1d.tokudb
-rw-rw----. 1 mysql mysql    32768 Apr  6 11:58 _graphite_dashboard_template_owners_main_461_2_1d.tokudb
-rw-rw----. 1 mysql mysql    65536 Apr 10 14:54 _graphite_dashboard_template_owners_status_461_1_1d.tokudb
-rw-rw----. 1 mysql mysql    16384 Apr  6 11:58 _graphite_dashboard_template_status_457_1_1d.tokudb
-rw-rw----. 1 mysql mysql    32768 Apr  6 11:58 _graphite_django_admin_log_key_django_admin_log_417f1b1c_46c_3_1d.tokudb
-rw-rw----. 1 mysql mysql    32768 Apr  6 11:58 _graphite_django_admin_log_key_django_admin_log_e8701ad4_46c_4_1d.tokudb
-rw-rw----. 1 mysql mysql    32768 Apr  6 11:58 _graphite_django_admin_log_main_46c_2_1d.tokudb
-rw-rw----. 1 mysql mysql    65536 Apr 10 14:54 _graphite_django_admin_log_status_46c_1_1d.tokudb
-rw-rw----. 1 mysql mysql    16896 Apr  6 11:58 _graphite_django_content_type_key_django_content_type_app_label_76bd3d3b_uniq_47e_1_1d_B_1.tokudb
-rw-rw----. 1 mysql mysql    16896 Apr  6 11:58 _graphite_django_content_type_main_47e_1_1d_B_0.tokudb
-rw-rw----. 1 mysql mysql    65536 Apr 10 14:54 _graphite_django_content_type_status_477_1_1d.tokudb
-rw-rw----. 1 mysql mysql    16896 Apr  6 11:58 _graphite_django_migrations_main_48b_1_1d_B_0.tokudb
-rw-rw----. 1 mysql mysql    16384 Apr  6 11:58 _graphite_django_migrations_status_484_1_1d.tokudb
-rw-rw----. 1 mysql mysql    32768 Apr  6 11:58 _graphite_django_session_key_django_session_de54fa62_491_3_1d.tokudb
-rw-rw----. 1 mysql mysql    32768 Apr  6 11:58 _graphite_django_session_main_491_2_1d.tokudb
-rw-rw----. 1 mysql mysql    65536 Apr 10 14:54 _graphite_django_session_status_491_1_1d.tokudb
-rw-rw----. 1 mysql mysql    32768 Apr  6 11:58 _graphite_events_event_main_49b_2_1d.tokudb
-rw-rw----. 1 mysql mysql    16384 Apr  6 11:58 _graphite_events_event_status_49b_1_1d.tokudb
-rw-rw----. 1 mysql mysql    32768 Apr  6 11:58 _graphite_tagging_taggeditem_key_tagging_taggeditem_417f1b1c_4b1_5_1d.tokudb
-rw-rw----. 1 mysql mysql    32768 Apr  6 11:58 _graphite_tagging_taggeditem_key_tagging_taggeditem_76f094bc_4b1_6_1d.tokudb
-rw-rw----. 1 mysql mysql    32768 Apr  6 11:58 _graphite_tagging_taggeditem_key_tagging_taggeditem_af31437c_4b1_4_1d.tokudb
-rw-rw----. 1 mysql mysql    32768 Apr  6 11:58 _graphite_tagging_taggeditem_key_tagging_taggeditem_tag_id_3d53f09d_uniq_4b1_3_1d.tokudb
-rw-rw----. 1 mysql mysql    32768 Apr  6 11:58 _graphite_tagging_taggeditem_main_4b1_2_1d.tokudb
-rw-rw----. 1 mysql mysql    65536 Apr 10 14:54 _graphite_tagging_taggeditem_status_4b1_1_1d.tokudb
-rw-rw----. 1 mysql mysql    32768 Apr  6 11:58 _graphite_tagging_tag_key_name_4a6_3_1d.tokudb
-rw-rw----. 1 mysql mysql    32768 Apr  6 11:58 _graphite_tagging_tag_main_4a6_2_1d.tokudb
-rw-rw----. 1 mysql mysql    65536 Apr 10 14:54 _graphite_tagging_tag_status_4a6_1_1d.tokudb
-rw-rw----. 1 mysql mysql  6160384 Apr 10 15:06 _graphite_tags_series_key_hash_4c3_1_1d_B_1.tokudb
-rw-rw----. 1 mysql mysql  7929856 Apr 10 15:06 _graphite_tags_series_main_4c3_1_1d_B_0.tokudb
-rw-rw----. 1 mysql mysql    65536 Apr 10 15:06 _graphite_tags_series_status_4bc_1_1d.tokudb
-rw-rw----. 1 mysql mysql   442368 Apr 10 15:06 _graphite_tags_seriestag_key_tags_seriestag_76f094bc_4d6_1_1d_B_3.tokudb
-rw-rw----. 1 mysql mysql   507904 Apr 10 15:06 _graphite_tags_seriestag_key_tags_seriestag_a08cee2d_4d6_1_1d_B_2.tokudb
-rw-rw----. 1 mysql mysql   507904 Apr 10 15:06 _graphite_tags_seriestag_key_tags_seriestag_b0304493_4d6_1_1d_B_4.tokudb
-rw-rw----. 1 mysql mysql   524288 Apr 10 15:06 _graphite_tags_seriestag_key_tags_seriestag_series_id_ad31c493_uniq_4d6_1_1d_B_1.tokudb
-rw-rw----. 1 mysql mysql   901120 Apr 10 15:06 _graphite_tags_seriestag_main_4d6_1_1d_B_0.tokudb
-rw-rw----. 1 mysql mysql    65536 Apr 10 15:06 _graphite_tags_seriestag_status_4cf_1_1d.tokudb
-rw-rw----. 1 mysql mysql  1179648 Apr 10 15:06 _graphite_tags_tagvalue_key_value_4f0_1_1d_B_1.tokudb
-rw-rw----. 1 mysql mysql  1736704 Apr 10 15:06 _graphite_tags_tagvalue_main_4f0_1_1d_B_0.tokudb
-rw-rw----. 1 mysql mysql    65536 Apr 10 15:06 _graphite_tags_tagvalue_status_4e9_1_1d.tokudb
-rw-rw----. 1 mysql mysql    32768 Apr  6 11:58 _graphite_url_shortener_link_main_4f8_2_1d.tokudb
-rw-rw----. 1 mysql mysql    16384 Apr  6 11:58 _graphite_url_shortener_link_status_4f8_1_1d.tokudb
-rw-rw----. 1 mysql mysql 79691776 Apr 10 02:38 ibdata1
-rw-rw----. 1 mysql mysql 50331648 Apr 10 02:38 ib_logfile0
-rw-rw----. 1 mysql mysql 50331648 Apr 10 02:36 ib_logfile1
drwx------. 2 mysql mysql     4096 Apr  6 11:58 kanboard
-rw-rw----. 1 mysql mysql    16896 Apr  6 11:58 _kanboard_settings_main_549_1_1d_B_0.tokudb
-rw-rw----. 1 mysql mysql    16384 Apr  6 11:58 _kanboard_settings_status_543_1_1d.tokudb
-rw-rw----. 1 mysql mysql    32768 Apr  6 11:58 _kanboard_user_has_notifications_key_user_has_notifications_ibfk_2_569_3_1d.tokudb
-rw-rw----. 1 mysql mysql    32768 Apr  6 11:58 _kanboard_user_has_notifications_main_569_2_1d.tokudb
-rw-rw----. 1 mysql mysql    32768 Apr  6 11:58 _kanboard_user_has_notifications_status_569_1_1d.tokudb
-rw-------. 1 mysql mysql 26236353 Apr 10 15:09 log000000000006.tokulog29
drwx------. 2 mysql mysql      119 Apr  6 11:58 maxscale_schema
-rw-rw----. 1 mysql mysql        0 Apr  5 17:39 multi-master.info
drwx------. 2 mysql root      4096 Apr  6 09:59 mysql
drwx------. 2 mysql mysql       20 Apr  5 17:36 performance_schema
-rw-rw----. 1 mysql mysql        6 Apr  5 17:39 platformdb-isa-01.pid
-rw-rw----. 1 mysql mysql    32768 Apr  6 11:58 tokudb.directory
-rw-rw----. 1 mysql mysql    16384 Apr  5 17:22 tokudb.environment
-rw-------. 1 mysql mysql        0 Apr  5 17:22 __tokudb_lock_dont_delete_me_data
-rw-------. 1 mysql mysql        0 Apr  5 17:22 __tokudb_lock_dont_delete_me_environment
-rw-------. 1 mysql mysql        0 Apr  5 17:22 __tokudb_lock_dont_delete_me_logs
-rw-------. 1 mysql mysql        0 Apr  5 17:22 __tokudb_lock_dont_delete_me_recovery
-rw-------. 1 mysql mysql        0 Apr  5 17:22 __tokudb_lock_dont_delete_me_temp
-rw-rw----. 1 mysql mysql    32768 Apr 10 15:06 tokudb.rollback

我最近打破了复制，当我试图通过一个不正确的事务时。我得到了以下内容。

MariaDB [(none)]> STOP SLAVE;
Query OK, 0 rows affected (0.05 sec)

MariaDB [(none)]> SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;
ERROR 1966 (HY000): When using parallel replication and GTID with multiple replication domains, @@sql_slave_skip_counter cannot be used. Instead, setting @@gtid_slave_pos explicitly can be used to skip to after a given GTID position.
MariaDB [(none)]> select @@gtid_slave_pos;
+---------------------------------------------+
| @@gtid_slave_pos                            |
+---------------------------------------------+
| 0-1051-1391406,1-1050-1182069,57-1051-98897 |
+---------------------------------------------+
1 row in set (0.00 sec)

MariaDB [(none)]> show variables like '%_pos%';
+----------------------+---------------------------------------------------------+
| Variable_name        | Value                                                   |
+----------------------+---------------------------------------------------------+
| gtid_binlog_pos      | 0-1051-1391406,2-1051-4474,57-1051-98897                |
| gtid_current_pos     | 0-1051-1391406,1-1050-1182069,2-1051-4474,57-1051-98897 |
| gtid_slave_pos       | 0-1051-1391406,1-1050-1182069,57-1051-98897             |
| wsrep_start_position | 00000000-0000-0000-0000-000000000000:-1                 |
+----------------------+---------------------------------------------------------+

我需要做什么来解决这个问题。

更新 1

MariaDB [(none)]> show variables like '%gtid%';
+------------------------+------------------------------------------+
| Variable_name          | Value                                    |
+------------------------+------------------------------------------+
| gtid_binlog_pos        | 1-1050-4820789,2-1051-379101,3-1010-3273 |
| gtid_binlog_state      | 1-1050-4820789,2-1051-379101,3-1010-3273 |
| gtid_current_pos       | 1-1050-4819948,2-1051-379101,3-1010-3273 |
| gtid_domain_id         | 3                                        |
| gtid_ignore_duplicates | OFF                                      |
| gtid_seq_no            | 0                                        |
| gtid_slave_pos         | 1-1050-4819948,2-1051-379101,3-1010-3273 |
| gtid_strict_mode       | OFF                                      |
| last_gtid              |                                          |
| wsrep_gtid_domain_id   | 0                                        |
| wsrep_gtid_mode        | OFF                                      |
+------------------------+------------------------------------------+

我按照说明尝试了以下设置@@gtid_slave_pos;

MariaDB [(none)]> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: [redacted]
                  Master_User: [redacted]
                  Master_Port: 3306
                Connect_Retry: 5
              Master_Log_File: binary.000591
          Read_Master_Log_Pos: 526511543
               Relay_Log_File: tmsdb-relay-bin.001239
                Relay_Log_Pos: 4
        Relay_Master_Log_File: binary.000591
             Slave_IO_Running: 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: 1062
                   Last_Error: Could not execute Write_rows_v1 event on table [redacted] Duplicate entry '1134890' for key 'PRIMARY', Error_code: 1062; handler error HA_ERR_FOUND_DUPP_KEY; the event's master log binary.000591, end_log_pos 60726493
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 60724897
              Relay_Log_Space: 465787660
              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: 1062
               Last_SQL_Error: Could not execute Write_rows_v1 event on table [redacted] Duplicate entry '1134890' for key 'PRIMARY', Error_code: 1062; handler error HA_ERR_FOUND_DUPP_KEY; the event's master log binary.000591, end_log_pos 60726493
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 1050
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
                   Using_Gtid: Current_Pos
                  Gtid_IO_Pos: 1-1050-4827753,2-1051-379101,3-1010-3273
      Replicate_Do_Domain_Ids: 
  Replicate_Ignore_Domain_Ids: 
                Parallel_Mode: optimistic
1 row in set (0.00 sec)

使用 gtid_slave_pos 变量

MariaDB [(none)]> select @@gtid_slave_pos\G;
*************************** 1. row ***************************
@@gtid_slave_pos: 1-1050-4819948,2-1051-379101,3-1010-3273

MariaDB [(none)]> stop slave;
Query OK, 0 rows affected (0.21 sec)

MariaDB [(none)]> SET GLOBAL gtid_slave_pos='1-1050-4819948,2-1051-379101,3-1010-3274';
Query OK, 0 rows affected (0.10 sec)

MariaDB [(none)]> start slave;
Query OK, 0 rows affected (0.21 sec)

当我在运行上述后检查状态时Got fatal error 1236 from master when reading data from binary log: 'Error: connecting slave requested to start from GTID 3-1010-3274, which is not in the master's binlog'

MariaDB [(none)]> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: 
                  Master_Host: 10.56.228.64
                  Master_User: maxscale
                  Master_Port: 3306
                Connect_Retry: 5
              Master_Log_File: binary.000591
          Read_Master_Log_Pos: 60724897
               Relay_Log_File: tmsdb-relay-bin.001239
                Relay_Log_Pos: 4
        Relay_Master_Log_File: binary.000591
             Slave_IO_Running: No
            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: 60724897
              Relay_Log_Space: 249
              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: 1236
                Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'Error: connecting slave requested to start from GTID 3-1010-3274, which is not in the master's binlog'
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 1050
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
                   Using_Gtid: Current_Pos
                  Gtid_IO_Pos: 1-1050-4819948,2-1051-379101,3-1010-3274
      Replicate_Do_Domain_Ids: 
  Replicate_Ignore_Domain_Ids: 
                Parallel_Mode: optimistic
1 row in set (0.00 sec)

我可以通过

MariaDB [(none)]> stop slave;
Query OK, 0 rows affected (0.01 sec)

MariaDB [(none)]> SET GLOBAL gtid_slave_pos='1-1050-4819948,2-1051-379101,3-1010-3273';
Query OK, 0 rows affected (0.09 sec)

MariaDB [(none)]> start slave;
Query OK, 0 rows affected (0.06 sec)

maxscale 对内存/cpu 的预期生产要求是多少

我们有一个配置了 4 GB 内存的服务器，将 maxscale 作为查询 r/w 路由器运行，并在同一台服务器上运行复制管理器。

我发现在单个事务中进行大量插入，数百万行。具有 1000 万行的 5G 文件使用 LOAD 数据 infile。针对后端服务器直接运行相同的数据 infile 负载，没有任何问题。

这是后端服务器上的 max_allowed_pa​​cket。

MariaDB [(none)]> SHOW GLOBAL VARIABLES LIKE 'max_allowed_packet'\G
*************************** 1. row ***************************
Variable_name: max_allowed_packet
        Value: 16777216

后端服务器有 32 GB 的内存，目前没有服务在使用它，因为我们仍在对其进行测试和调整配置。

最大规模服务器内存不足。如此大的插入集会导致 maxscale 崩溃。

我在客户端看到以下错误。

ERROR 2013 (HY000) at line 1: Lost connection to MySQL server during query  
ERROR 2013 (HY000) at line 1: Lost connection to MySQL server at 'reading initial communication packet', system error: 111  
ERROR 2013 (HY000) at line 1: Lost connection to MySQL server at 'reading initial communication packet', system error: 111  

在服务器端，我在最大比例日志中看到以下内容。

2017-10-16 19:06:32   notice : Started MaxScale log flusher.
2017-10-16 19:06:32   notice : MaxScale started with 7 server threads.
2017-10-16 19:15:14   notice : Waiting for housekeeper to shut down.
2017-10-16 19:15:15   notice : Finished MaxScale log flusher.
2017-10-16 19:15:15   notice : Housekeeper shutting down.
2017-10-16 19:15:15   notice : Housekeeper has shut down.
2017-10-16 19:15:15   notice : MaxScale received signal SIGTERM. Exiting.
2017-10-16 19:15:15   notice : MaxScale is shutting down.
2017-10-16 19:15:15   notice : MaxScale shutdown completed.
2017-10-16 19:15:15   MariaDB MaxScale is shut down.
----------------------------------------------------


MariaDB MaxScale  /var/log/maxscale/maxscale.log  Mon Oct 16 19:15:17 2017
----------------------------------------------------------------------------
2017-10-16 19:15:17   notice : Working directory: /var/log/maxscale
2017-10-16 19:15:17   notice : MariaDB MaxScale 2.1.5 started
2017-10-16 19:15:17   notice : MaxScale is running in process 21067
2017-10-16 19:15:17   notice : Configuration file: /etc/maxscale.cnf
2017-10-16 19:15:17   notice : Log directory: /var/log/maxscale
2017-10-16 19:15:17   notice : Data directory: /var/cache/maxscale
2017-10-16 19:15:17   notice : Module directory: /usr/lib64/maxscale
2017-10-16 19:15:17   notice : Service cache: /var/cache/maxscale
2017-10-16 19:15:17   notice : Loading /etc/maxscale.cnf.
2017-10-16 19:15:17   notice : /etc/maxscale.cnf.d does not exist, not reading.
2017-10-16 19:15:17   notice : Loaded module ccrfilter: V1.1.0 from /usr/lib64/maxscale/libccrfilter.so
2017-10-16 19:15:17   notice : [cli] Initialise CLI router module
2017-10-16 19:15:17   notice : Loaded module cli: V1.0.0 from /usr/lib64/maxscale/libcli.so
2017-10-16 19:15:17   notice : [readwritesplit] Initializing statement-based read/write split router module.
2017-10-16 19:15:17   notice : Loaded module readwritesplit: V1.1.0 from /usr/lib64/maxscale/libreadwritesplit.so
2017-10-16 19:15:17   notice : [mysqlmon] Initialise the MySQL Monitor module.
2017-10-16 19:15:17   notice : Loaded module mysqlmon: V1.5.0 from /usr/lib64/maxscale/libmysqlmon.so
2017-10-16 19:15:17   notice : Loaded module MySQLBackend: V2.0.0 from /usr/lib64/maxscale/libMySQLBackend.so
2017-10-16 19:15:17   notice : Loaded module MySQLBackendAuth: V1.0.0 from /usr/lib64/maxscale/libMySQLBackendAuth.so
2017-10-16 19:15:17   notice : Loaded module maxscaled: V2.0.0 from /usr/lib64/maxscale/libmaxscaled.so
2017-10-16 19:15:17   notice : Loaded module MaxAdminAuth: V2.1.0 from /usr/lib64/maxscale/libMaxAdminAuth.so
2017-10-16 19:15:17   notice : Loaded module MySQLClient: V1.1.0 from /usr/lib64/maxscale/libMySQLClient.so
2017-10-16 19:15:17   notice : Loaded module MySQLAuth: V1.1.0 from /usr/lib64/maxscale/libMySQLAuth.so
2017-10-16 19:15:17   notice : No query classifier specified, using default 'qc_sqlite'.
2017-10-16 19:15:17   notice : Loaded module qc_sqlite: V1.0.0 from /usr/lib64/maxscale/libqc_sqlite.so
2017-10-16 19:15:17   notice : Encrypted password file /var/cache/maxscale/.secrets can't be accessed (No such file or directory). Password encryption is not used.
2017-10-16 19:15:17   notice : [MySQLAuth] [Read-Write_Service] Loaded 227 MySQL users for listener Read-Write_Listener.
2017-10-16 19:15:17   notice : Listening for connections at [10.56.229.60]:3306 with protocol MySQL
2017-10-16 19:15:17   notice : Listening for connections at [::]:6603 with protocol MaxScale Admin
2017-10-16 19:15:17   notice : Started MaxScale log flusher.
2017-10-16 19:15:17   notice : MaxScale started with 7 server threads.
2017-10-16 19:15:17   notice : Server changed state: tmsdb-isa-01[10.56.228.64:3306]: new_master. [Running] -> [Master, Running]
2017-10-16 19:15:17   notice : Server changed state: tmsdb-isa-02[10.56.228.65:3306]: new_slave. [Running] -> [Slave, Running]
2017-10-16 19:15:17   notice : Server changed state: tmsdb-rp-01[10.21.228.65:3306]: new_slave. [Running] -> [Slave, Running]
2017-10-16 19:15:17   notice : [mysqlmon] A Master Server is now available: 10.56.228.64:3306

我运行 VMSTAT 并注意到服务器内存不足

[root@maxscale-isa-02 ~]# vmstat 
procs -----------memory---------- ---swap-- -----io---- -system-- ------cpu-----
 r  b   swpd   free   buff  cache   si   so    bi    bo   in   cs us sy id wa st
 1  0      0 1485828      0 356652    0    0     1     0    0    0  0  0 100  0  0
    [root@maxscale-isa-02 ~]# vmstat 
procs -----------memory---------- ---swap-- -----io---- -system-- ------cpu-----
 r  b   swpd   free   buff  cache   si   so    bi    bo   in   cs us sy id wa st
 1  0      0 368000      0 356496    0    0     1     0    0    0  0  0 100  0  0
[root@maxscale-isa-02 ~]# vmstat 
procs -----------memory---------- ---swap-- -----io---- -system-- ------cpu-----
 r  b   swpd   free   buff  cache   si   so    bi    bo   in   cs us sy id wa st
 2  0      0 173388      0 356512    0    0     1     0    0    0  0  0 100  0  0
[root@maxscale-isa-02 ~]# vmstat 
procs -----------memory---------- ---swap-- -----io---- -system-- ------cpu-----
 r  b   swpd   free   buff  cache   si   so    bi    bo   in   cs us sy id wa st
 1  1      0 103660      0 308088    0    0     1     0    0    0  0  0 100  0  0
[root@maxscale-isa-02 ~]# vmstat 
-bash: fork: Cannot allocate memory
[root@maxscale-isa-02 ~]# vmstat 
-bash: fork: Cannot allocate memory
[root@maxscale-isa-02 ~]# vmstat 
-bash: fork: Cannot allocate memory
[root@maxscale-isa-02 ~]# vmstat 
procs -----------memory---------- ---swap-- -----io---- -system-- ------cpu-----
 r  b   swpd   free   buff  cache   si   so    bi    bo   in   cs us sy id wa st
 1  0      0 2478676      0 323508    0    0     1     0    0    0  0  0 100  0  0

编辑

我们为最大规模服务器添加了额外的 4G RAM，总共 4GB 到 8GB。

我们现在可以看到，它可以在导入过程中读取高达 5G 的内存而不会崩溃。
这表明运行 maxscale 的服务器将需要与通过服务器运行查询的所有服务在峰值内存使用时所需的内存一样多。使用ReadWriteSplit路由器时。

我们将测试ReadConnRoute路由器，看看我们是否可以降低内存使用要求。

我正在尝试运行以下内容。

mysql > purge binary logs to 'mysqld-relay-bin.000075';
ERROR 1373 (HY000): Target log not found in binlog index

# cat ./mysqld-relay-bin.index 
/srv/mysql/logs/mysqld-relay-bin.000010
....
/srv/mysql/logs/mysqld-relay-bin.000075
/srv/mysql/logs/mysqld-relay-bin.000076
/srv/mysql/logs/mysqld-relay-bin.000077
/srv/mysql/logs/mysqld-relay-bin.000078
/srv/mysql/logs/mysqld-relay-bin.000079

我该怎么做才能手动清除删除这些中继日志。

MariaDB [(none)]> show slave status;
Empty set (0.00 sec)

MariaDB [(none)]> show master status;
+---------------+-----------+--------------+------------------+
| File          | Position  | Binlog_Do_DB | Binlog_Ignore_DB |
+---------------+-----------+--------------+------------------+
| binary.000141 | 487953618 |              |                  |
+---------------+-----------+--------------+------------------+


ls -lrt /var/lib/mysql/logs/
total 4616604
-rw-rw---- 1 mysql mysql        299 Jun  7 15:04 mysqld-relay-bin.000010
-rw-rw---- 1 mysql mysql        299 Jun  7 15:19 mysqld-relay-bin.000011
-rw-rw---- 1 mysql mysql        299 Jun  7 15:21 mysqld-relay-bin.000012
....
-rw-rw---- 1 mysql mysql        299 Jul 23 01:15 mysqld-relay-bin.000075
-rw-rw---- 1 mysql mysql        299 Jul 23 01:15 mysqld-relay-bin.000076
-rw-rw---- 1 mysql mysql        268 Jul 24 09:17 mysqld-relay-bin.000077

作为我运行的备份脚本的一部分。我每天晚上都刷新日志，这会不会有影响。

复制日志堆积的主服务器。

MariaDB [(none)]> SHOW VARIABLES LIKE 'server_id';  
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id     | 1000  |
+---------------+-------+
1 row in set (0.00 sec)

MariaDB [(none)]> SHOW VARIABLES LIKE '%relay%';
+-----------------------+----------------------------------------+
| Variable_name         | Value                                  |
+-----------------------+----------------------------------------+
| max_relay_log_size    | 1073741824                             |
| relay_log             | /srv/mysql/logs/mysqld-relay-bin       |
| relay_log_basename    | /srv/mysql/logs/mysqld-relay-bin       |
| relay_log_index       | /srv/mysql/logs/mysqld-relay-bin.index |
| relay_log_info_file   | relay-log.info                         |
| relay_log_purge       | ON                                     |
| relay_log_recovery    | OFF                                    |
| relay_log_space_limit | 0                                      |
| sync_relay_log        | 10000                                  |
| sync_relay_log_info   | 10000                                  |
+-----------------------+----------------------------------------+

连接到主站的从站。

MariaDB [(none)]> SHOW VARIABLES LIKE 'server_id';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id     | 1002  |
+---------------+-------+
1 row in set (0.00 sec)


MariaDB [(none)]>  SHOW SLAVE STATUS\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 10.21.228.81
                  Master_User: db.replicator
                  Master_Port: 3306
                Connect_Retry: 10
              Master_Log_File: binary.000141
          Read_Master_Log_Pos: 540479720
               Relay_Log_File: mysqld-relay-bin.000358
                Relay_Log_Pos: 540480005
        Relay_Master_Log_File: binary.000141
             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: 540479720
              Relay_Log_Space: 540480344
              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: 1000
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
                   Using_Gtid: No
                  Gtid_IO_Pos: 
      Replicate_Do_Domain_Ids: 
  Replicate_Ignore_Domain_Ids: 
                Parallel_Mode: conservative
1 row in set (0.00 sec)

ERROR: No query specified

我想备份一个 tokudb 数据库。

我希望能够将它恢复到最近的状态。我正在备份时写入文件。

我的猜测是运行这样的东西

mysql -e "SHOW MASTER STATUS; SHOW SLAVE STATUS; FLUSH LOGS" > mylogposition.log

哪个会在保存位置后轮换日志文件？

MySQL 无法在 Arch Linux 上启动。

mysqld[3440]: [Note] /usr/bin/mysqld (mysqld 10.1.22-MariaDB) starting as process 3440 ...
mysqld[3440]: [Note] InnoDB: Using mutexes to ref count buffer pool pages
mysqld[3440]: [Note] InnoDB: The InnoDB memory heap is disabled
mysqld[3440]: [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
mysqld[3440]: [Note] InnoDB: GCC builtin __atomic_thread_fence() is used for memory barrier
mysqld[3440]: [Note] InnoDB: Compressed tables use zlib 1.2.11
mysqld[3440]: [Note] InnoDB: Using Linux native AIO
mysqld[3440]: [Note] InnoDB: Using SSE crc32 instructions
mysqld[3440]: [Note] InnoDB: Initializing buffer pool, size = 128.0M
mysqld[3440]: [Note] InnoDB: Completed initialization of buffer pool
mysqld[3440]: [Note] InnoDB: Highest supported file format is Barracuda.
mysqld[3440]: [Note] InnoDB: The log sequence numbers 0 and 0 in ibdata files do not match the log sequence number 1600719 in the ib_logfiles!
mysqld[3440]: [Note] InnoDB: Restoring possible half-written data pages from the doublewrite buffer...
mysqld[3440]: [Note] InnoDB: 128 rollback segment(s) are active.
mysqld[3440]: [Note] InnoDB: Waiting for purge to start
mysqld[3440]: [Note] InnoDB:  Percona XtraDB (http://www.percona.com) 5.6.35-80.0 started; log sequence number 1600719
mysqld[3440]: [Note] InnoDB: Dumping buffer pool(s) not yet started
mysqld[3440]: [Note] Plugin 'FEEDBACK' is disabled.
mysqld[3440]: [ERROR] Could not open mysql.plugin table. Some plugins may be not loaded
mysqld[3440]: [Note] Recovering after a crash using mysql-bin
mysqld[3440]: [Note] Starting crash recovery...
mysqld[3440]: [Note] Crash recovery finished.
mysqld[3440]: [Warning] Failed to load slave replication state from table mysql.gtid_slave_pos: 1146: Table 'mysql.gtid_slave_pos' doesn't exist
mysqld[3440]: [ERROR] Can't open and lock privilege tables: Table 'mysql.servers' doesn't exist
mysqld[3440]: [Note] Server socket created on IP: '::'.
mysqld[3440]: [ERROR] Fatal error: Can't open and lock privilege tables: Table 'mysql.user' doesn't exist

我已经重新安装了这些软件包，但我仍然遇到了同样的问题。

我正忙于构建现有数据库的奴隶。我不希望它在将奴隶带入与主人相同的状态之前为我导入的数据构建 bin 日志。

这主要是为了节省导入 100 G 数据的空间。

mysqldump somelargedb | gzip > /somewhere/withspace/dump/somelargedb.sql.gz

未压缩此文件在 100 Gb 范围内。压缩后约为 2Gb

我正在尝试创建一些 tokudb 表来试验不同的行格式选项，以比较可用的压缩。

https://www.percona.com/doc/percona-server/5.7/tokudb/using_tokudb.html

我已经尝试了以下所有
TOKUDB_SNAPPY
TOKUDB_ZLIB
TOKUDB_DEFAULT

没有效果。

如果我只是忽略它，则表是使用 row_fromat = fixed 创建的。

MariaDB [eventlog]> show VARIABLES like "%row_format%";
+--------------------------------+-------------+
| Variable_name                  | Value       |
+--------------------------------+-------------+
| tokudb_hide_default_row_format | ON          |
| tokudb_row_format              | tokudb_zlib |
+--------------------------------+-------------+



MariaDB [eventlog]> CREATE TABLE stable1 ( column_a INT NOT NULL PRIMARY KEY, column_b INT NOT NULL) ENGINE=TokuDB, ROW_FORMAT=TOKUDB_DEFAULT;  
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'TOKUDB_DEFAULT' at line 1    
MariaDB [eventlog]> CREATE TABLE stable1 ( column_a INT NOT NULL PRIMARY KEY, column_b INT NOT NULL) ROW_FORMAT=tokudb_default;   
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'tokudb_default' at line 1


MariaDB [eventlog]> CREATE TABLE stable1 ( column_a INT NOT NULL PRIMARY KEY, column_b INT NOT NULL) ENGINE=TokuDB;
Query OK, 0 rows affected (0.09 sec)


MariaDB [eventlog]> show table status from eventlog\G;
*************************** 1. row ***************************
           Name: stable1
         Engine: TokuDB
        Version: 10
     Row_format: Fixed
           Rows: 0
 Avg_row_length: 0
    Data_length: 0
Max_data_length: 9223372036854775807
   Index_length: 0
      Data_free: 18446744073709551615
 Auto_increment: NULL
    Create_time: 2017-02-20 12:26:18
    Update_time: 2017-02-20 12:26:18
     Check_time: NULL
      Collation: latin1_swedish_ci
       Checksum: NULL
 Create_options: 
        Comment: 


 MariaDB [eventlog]> ALTER TABLE stable1 ROW_FORMAT=TOKUDB_SNAPPY;  
 ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'TOKUDB_SNAPPY' at line 1


 version | 10.1.21-MariaDB  
 tokudb_version | 5.6.34-79.1 

我想导入这个 csv 示例数据：

��SendDateTime;ExternalMessageId;SMSCount;Account Name;Country;Country Prefix;Network;Destination Address;Sender;Price Per Message;Credits Per Message;Parent AccountId;Parent Credits Per Message;Client Metadata;Status;Reason;MessageText;DR Arrival Time
21.2.2016. 0:01:17;="126022022011749133";1;some-account;some coutry;27;somenetwork;="27123456789";="27123456789";90,0000;90,0000;;;="";Delivered;;Confirmation: some sms text value;21.2.2016. 0:01:19;

这是来自http://www.infobip.com/导出的报告的示例。为什么 infobip 将其称为 csv 我不知道。

我在使用以下90,0000货币值时遇到问题。

如何将其转换为FLOAD/DECIMAL作为导入的一部分。