We’re always testing the latest versions of MySQL with most of the environments to make sure that we can find the critical issues before it goes to production. This wasn’t different with the 5.6 MySQL neither. We already started to play with this version in the summer. The first news were very promising. The performance gain is significant. However we have run into couple of errors. One of them was the
mysql_install_db problem with replicating environment
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 |
[root@hostname ~]# mysql_install_db --user=mysql --datadir=/mysql/data/ ------------------------------------------------------------------------------------ Installing MySQL system tables... 121217 10:02:20 InnoDB: The InnoDB memory heap is disabled 121217 10:02:20 InnoDB: Mutexes and rw_locks use GCC atomic builtins 121217 10:02:20 InnoDB: Compressed tables use zlib 1.2.3 121217 10:02:20 InnoDB: Using Linux native AIO 121217 10:02:20 InnoDB: CPU supports crc32 instructions 121217 10:02:20 InnoDB: Initializing buffer pool, size = 76.0G 121217 10:02:23 InnoDB: Completed initialization of buffer pool InnoDB: The first specified data file ./ibdata1 did not exist: InnoDB: a new database to be created! 121217 10:02:23 InnoDB: Setting file ./ibdata1 size to 10 MB InnoDB: Database physically writes the file full: wait... 121217 10:02:24 InnoDB: Log file ./ib_logfile0 did not exist: new to be created InnoDB: Setting log file ./ib_logfile0 size to 512 MB InnoDB: Database physically writes the file full: wait... InnoDB: Progress in MB: 100 200 300 400 500 121217 10:02:25 InnoDB: Log file ./ib_logfile1 did not exist: new to be created InnoDB: Setting log file ./ib_logfile1 size to 512 MB InnoDB: Database physically writes the file full: wait... InnoDB: Progress in MB: 100 200 300 400 500 121217 10:02:26 InnoDB: Doublewrite buffer not found: creating new 121217 10:02:26 InnoDB: Doublewrite buffer created 121217 10:02:26 InnoDB: 128 rollback segment(s) are active. InnoDB: Creating foreign key constraint system tables InnoDB: Foreign key constraint system tables created 121217 10:02:26 InnoDB: Waiting for the background threads to start 121217 10:02:26 InnoDB: 1.2.5 started; log sequence number 0 121217 10:02:26 [Warning] Info table is not ready to be used. Table 'mysql.slave_master_info' cannot be opened. 121217 10:02:26 [Warning] Error while checking replication metadata. Setting the requested repository in order to give users the chance to fix the problem and restart the server. If this is a live upgrade please consider using mysql_upgrade to fix the problem. 121217 10:02:26 [Warning] Info table is not ready to be used. Table 'mysql.slave_relay_log_info' cannot be opened. 121217 10:02:26 [Warning] Error while checking replication metadata. Setting the requested repository in order to give users the chance to fix the problem and restart the server. If this is a live upgrade please consider using mysql_upgrade to fix the problem. 09:02:26 UTC - mysqld got signal 11 ; This could be because you hit a bug. It is also possible that this binary or one of the libraries it was linked against is corrupt, improperly built, or misconfigured. This error can also be caused by malfunctioning hardware. We will try our best to scrape up some info that will hopefully help diagnose the problem, but since we have already crashed, something is definitely wrong and this may fail. key_buffer_size=805306368 read_buffer_size=4194304 max_used_connections=0 max_threads=3000 thread_count=1 connection_count=0 It is possible that mysqld could use up to key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 25402275 K bytes of memory Hope that's ok; if not, decrease some variables in the equation. Thread pointer: 0x82b5c7d0 Attempting backtrace. You can use the following information to find out where mysqld died. If you see no messages after this, something went terribly wrong... stack_bottom = 7f6ccf277de8 thread_stack 0x40000 /usr/sbin/mysqld(my_print_stacktrace+0x35)[0x8920a5] /usr/sbin/mysqld(handle_fatal_signal+0x4a4)[0x644214] /lib64/libpthread.so.0(+0xf500)[0x7f811e010500] /usr/sbin/mysqld(_ZN11Group_cache22generate_automatic_gnoEP3THD+0xb4)[0x826974] /usr/sbin/mysqld(_Z23gtid_before_write_cacheP3THDP17binlog_cache_data+0x22b)[0x84344b] /usr/sbin/mysqld(_ZN13MYSQL_BIN_LOG11write_eventEP9Log_event+0x54b)[0x84a46b] /usr/sbin/mysqld(_ZN3THD12binlog_queryENS_22enum_binlog_query_typeEPKcmbbbi+0xb9)[0x84ac49] /usr/sbin/mysqld(_Z13write_bin_logP3THDbPKcmb+0x5d)[0x71f91d] /usr/sbin/mysqld(_Z18mysql_create_tableP3THDP10TABLE_LISTP24st_ha_create_informationP10Alter_info+0xf7)[0x726d17] /usr/sbin/mysqld(_Z21mysql_execute_commandP3THD+0x44a5)[0x6d8e55] /usr/sbin/mysqld(_Z11mysql_parseP3THDPcjP12Parser_state+0x30f)[0x6d95df] /usr/sbin/mysqld[0x6d9e6c] /usr/sbin/mysqld(_Z19do_handle_bootstrapP3THD+0x14a)[0x6da23a] /usr/sbin/mysqld(handle_bootstrap+0x47)[0x6da297] /usr/sbin/mysqld(pfs_spawn_thread+0x139)[0xa7a2b9] /lib64/libpthread.so.0(+0x7851)[0x7f811e008851] /lib64/libc.so.6(clone+0x6d)[0x7f811cd7211d] Trying to get some variables. Some pointers may be invalid and cause the dump to abort. Query (7f6cc00009c0): is an invalid pointer Connection ID (thread ID): 1 Status: NOT_KILLED The manual page at http://dev.mysql.com/doc/mysql/en/crashing.html contains information that should help you find out what is causing the crash. Installation of system tables failed! Examine the logs in /mysql/data/ for more information. You can try to start the mysqld daemon with: shell> /usr/sbin/mysqld --skip-grant-tables & and use the command line tool /usr/bin/mysql to connect to the mysql database and look at the grant tables: shell> /usr/bin/mysql -u root mysql mysql> show tables Try 'mysqld --help' if you have problems with paths. Using --log gives you a log in /mysql/data/ that may be helpful. Please consult the MySQL manual section 'Problems running mysql_install_db', and the manual section that describes problems on your OS. Another information source are the MySQL email archives available at http://lists.mysql.com/. Please check all of the above before mailing us! And remember, if you do mail us, you MUST use the /usr/bin/mysqlbug script! ------------------------------------------------------------------------------------------------ This is not helping: --master-info-repository=FILE --relay-log-info-repository=FILE Solution (A) removing the replication related config from my.cnf can help [root@hostname ~]# diff /etc/my.cnf /etc/my.cnf.old 21,25c21,25 < # log-bin = ../log/binlog < # binlog_format = ROW < # sync_binlog = 1 < # expire_logs_days = 3 < # log_slave_updates --- > log-bin = ../log/binlog > binlog_format = ROW > sync_binlog = 1 > expire_logs_days = 3 > log_slave_updates 27c27 < # relay_log = ../log/relaylog --- > relay_log = ../log/relaylog 79,81c79,81 < #gtid_mode = ON < #disable-gtid-unsafe-statements < # binlog_row_image = minimal --- > gtid_mode = ON > disable-gtid-unsafe-statements > binlog_row_image = minimal Solution (B) Running mysql_install_db without the my.cnf file so it won't look for slave_info. mysql_install_db --user=mysql --datadir=/mysql/data/ --no-defaults --innodb-log-file-size=$(grep innodb_log_file_size /etc/my.cnf|awk -F "=" '{print $2}'|sed -e 's/ //') The datadir and innodb_log_file_size are the only parameters that are really essential. Without the log_file_size the myslq_install_db will create the default sized file which is usually (hopefully) different from the configured size. I think I don't need the explain the necessity of the datadir variable. Downside of this solution is that the mysql_install_db will run with very different settings than the MySQL afterwards. Note: Check innodb_data_file_path variable if the autoextend is not in it you should define it for the script just like you did with the log_file_size. |
The important part is this:
Info table is not ready to be used. Table ‘mysql.slave_master_info’ cannot be opened.
Info table is not ready to be used. Table ‘mysql.slave_relay_log_info‘ cannot be opened.
A new feature in MySQL 5.6 is to store the replication info in a table rather than the master.info and relay.info files like the previous versions did. This makes the slave more durable (the D from the ACID).
Nevertheless this only happens with configured slave this is quite annoying. In a centrally configured environment (puppet for example) the my.cnf is already in place when you want to run the mysql_install_db.
Solution A
Backup the my.cnf file and remove all the replication related config from it. After the successful run you can put back to old file.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 |
[root@hostname ~]# diff /etc/my.cnf /etc/my.cnf.old 21,25c21,25 < # log-bin = ../log/binlog < # binlog_format = ROW < # sync_binlog = 1 < # expire_logs_days = 3 < # log_slave_updates --- > log-bin = ../log/binlog > binlog_format = ROW > sync_binlog = 1 > expire_logs_days = 3 > log_slave_updates 27c27 < # relay_log = ../log/relaylog --- > relay_log = ../log/relaylog 79,81c79,81 < #gtid_mode = ON < #disable-gtid-unsafe-statements < # binlog_row_image = minimal --- > gtid_mode = ON > disable-gtid-unsafe-statements > binlog_row_image = minimal |
Solution B
The only problem with the previous solution is that it cannot be easily scripted and the mysql_install_db cannot be integrated smoothly in an automated provisioning process. Running mysql_install_db without the my.cnf file won’t look for slave_info so the problem solved. However there are some magic still need to be added (explained below).
|
1 |
mysql_install_db --user=mysql --datadir=/mysql/data/ --no-defaults --innodb-log-file-size=$(grep innodb_log_file_size /etc/my.cnf|awk -F "=" '{print $2}'|sed -e 's/ //') |
The datadir and innodb_log_file_size are the only parameters that are really essential. Without the log_file_size the myslq_install_db will create the default sized file which is usually (hopefully) different from the configured size. I think I don’t need the explain the necessity of the datadir variable.
Downside of this solution is that the mysql_install_db will run with very different settings than the MySQL afterwards.
Note: Check innodb_data_file_path variable if the autoextend is not in it you should define it for the script just like you did with the log_file_size.
Recent comments