{"id":3166,"date":"2019-06-11T09:06:02","date_gmt":"2019-06-11T08:06:02","guid":{"rendered":"http:\/\/dasini.net\/blog\/?p=3166"},"modified":"2019-06-11T09:18:28","modified_gmt":"2019-06-11T08:18:28","slug":"check-the-mysql-server-startup-configuration","status":"publish","type":"post","link":"https:\/\/dasini.net\/blog\/2019\/06\/11\/check-the-mysql-server-startup-configuration\/","title":{"rendered":"Check the MySQL server startup configuration"},"content":{"rendered":"\n<div class=\"wp-block-image\"><figure class=\"aligncenter\"><img data-recalc-dims=\"1\" decoding=\"async\" src=\"https:\/\/i0.wp.com\/dasini.net\/blog\/wp-content\/uploads\/caribeean_800x336_by_olivier_dasini.png\" alt=\"Caribbean by Olivier DASINI\"\/><\/figure><\/div>\n\n\n\n<p>Since 8.0.<strong>16<\/strong>, MySQL Server supports a <strong><a rel=\"noreferrer noopener\" aria-label=\"validate-config (opens in a new tab)\" href=\"https:\/\/dev.mysql.com\/doc\/refman\/8.0\/en\/server-options.html#option_mysqld_validate-config\" target=\"_blank\">validate-config<\/a><\/strong> option that enables the startup configuration to be <u>checked for problems<\/u> without running the server in normal operational mode:<\/p>\n\n\n\n<ul class=\"wp-block-list\"><li>If no errors are found, the server terminates with an exit code of 0. <\/li><li>If an error is found, the server displays a diagnostic message and terminates with an exit code of 1.<\/li><\/ul>\n\n\n\n<p><strong>validate-config<\/strong> can be used any time, but is particularly <u>useful after an upgrade<\/u>, to check whether any options previously used with the older server are considered by the upgraded server to be deprecated or obsolete. <\/p>\n\n\n\n\n\n<p>First let&rsquo;s get some information about my MySQL version and configuration.<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>$ mysqld --help --verbose | head -n13\nmysqld  Ver 8.0.16 for Linux on x86_64 (MySQL Community Server - GPL)\nCopyright (c) 2000, 2019, Oracle and\/or its affiliates. All rights reserved.\n\nOracle is a registered trademark of Oracle Corporation and\/or its\naffiliates. Other names may be trademarks of their respective\nowners.\n\nStarts the MySQL database server.\n\nUsage: mysqld [OPTIONS]\n\nDefault options are read from the following files in the given order:\n\/etc\/my.cnf \/etc\/mysql\/my.cnf \/usr\/etc\/my.cnf ~\/.my.cnf <\/code><\/pre>\n\n\n\n<p>I&rsquo;m using MySQL 8.0.16.<br>The default options configuration are read in the given order from :<\/p>\n\n\n\n<ul class=\"wp-block-list\"><li> \/etc\/my.cnf <\/li><li> \/etc\/mysql\/my.cnf <\/li><li> \/usr\/local\/mysql\/etc\/my.cnf <\/li><li> ~\/.my.cnf <\/li><\/ul>\n\n\n\n\n\n<p>Now let&rsquo;s check my MySQL server startup configuration :<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>$ mysqld --validate-config\n$<\/code><\/pre>\n\n\n\n<p>No error ! <br>No output, everything looks good. <br>My server will start with this configuration.<\/p>\n\n\n\n\n\n<p>If there is an error, the server terminates. <br>The output is obviously different :<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>$ mysqld --validate-config --fake-option\n2019-06-05T15:10:08.653775Z 0 [ERROR] [MY-000068] [Server] unknown option '--fake-option'.\n2019-06-05T15:10:08.653822Z 0 [ERROR] [MY-010119] [Server] Aborting<\/code><\/pre>\n\n\n\n\n\n<p>Usually your configuration options are written in your configuration file (in general named my.cnf).<br>Therefore you can also use <strong>validate-config<\/strong> in this context :<br><\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>$ mysqld --defaults-file=\/etc\/my.cnf --validate-config \n$ <\/code><\/pre>\n\n\n\n<blockquote class=\"wp-block-quote is-layout-flow wp-block-quote-is-layout-flow\"><p><u>Note<\/u>:<\/p><p>defaults-file, if specified, must be <strong>the first option<\/strong> on the command line.<\/p><\/blockquote>\n\n\n\n\n\n<p>Furthermore you can handle the verbosity using <a rel=\"noreferrer noopener\" aria-label=\"log_error_verbosity (opens in a new tab)\" href=\"https:\/\/dev.mysql.com\/doc\/refman\/8.0\/en\/server-system-variables.html#sysvar_log_error_verbosity\" target=\"_blank\">log_error_verbosity<\/a> : <\/p>\n\n\n\n<ul class=\"wp-block-list\"><li>A value of 1 gives you ERROR<\/li><li>A value of 2 gives you ERROR &amp; WARNING<\/li><li>A value of 3 gives you ERROR, WARNING &amp; INFORMATION (i.e. note)<\/li><\/ul>\n\n\n\n<p>With a verbosity of 2, in addition to errors, we will be able to display warnings : <\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>$ mysqld --defaults-file=\/etc\/my.cnf --validate-config  --log_error_verbosity=2\n2019-06-05T15:53:42.785422Z 0 [Warning] [MY-011068] [Server] The syntax 'expire-logs-days' is deprecated and will be removed in a future release. Please use binlog_expire_logs_seconds instead.\n2019-06-05T15:53:42.785660Z 0 [Warning] [MY-010101] [Server] Insecure configuration for --secure-file-priv: Location is accessible to all OS users. Consider choosing a different directory.<\/code><\/pre>\n\n\n\n<p>Nothing very serious, however it is a best practice to delete warnings, when possible.<\/p>\n\n\n\n<p>So I fixed these warnings :<\/p>\n\n\n\n<ul class=\"wp-block-list\"><li>Use <a rel=\"noreferrer noopener\" aria-label=\"binlog_expire_logs_seconds (opens in a new tab)\" href=\"https:\/\/dev.mysql.com\/doc\/refman\/8.0\/en\/replication-options-binary-log.html#sysvar_binlog_expire_logs_seconds\" target=\"_blank\">binlog_expire_logs_seconds<\/a> instead of expire-logs-days <\/li><li>Setup the right permissions for my directory <a rel=\"noreferrer noopener\" aria-label=\"secure-file-priv (opens in a new tab)\" href=\"https:\/\/dev.mysql.com\/doc\/refman\/8.0\/en\/server-system-variables.html#sysvar_secure_file_priv\" target=\"_blank\">secure-file-priv<\/a> <\/li><\/ul>\n\n\n\n<pre class=\"wp-block-code\"><code>$ mysqld --defaults-file=\/etc\/my.cnf --validate-config  --log_error_verbosity=2\n2019-06-05T16:04:32.363297Z 0 [ERROR] [MY-000067] [Server] unknown variable 'binlog_expire_logs_second=7200'.\n2019-06-05T16:04:32.363369Z 0 [ERROR] [MY-010119] [Server] Aborting<\/code><\/pre>\n\n\n\n<p>Oops!!! There is a typo&#8230; :-0 <br>I wrote <em>binlog_expire_logs_second<\/em> instead of <em>binlog_expire_logs_second<\/em><strong><em>s<\/em><\/strong>.<br><em>(I forgot the final \u00ab\u00a0s\u00a0\u00bb)<\/em><\/p>\n\n\n\n<p>In that case, my MySQL server could not start. <br>Thanks to <strong>validate-config<\/strong> !<br>I can now avoid some unpleasant experience when starting the server \ud83d\ude42<\/p>\n\n\n\n<p>With the correct spelling I have now no error and no warning :<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>$ mysqld --defaults-file=\/etc\/my.cnf --validate-config  --log_error_verbosity=2\n$ <\/code><\/pre>\n\n\n\n\n\n<p>Note that you could also use verbosity 3<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>$ mysqld --defaults-file=\/etc\/my.cnf --validate-config  --log_error_verbosity=3\n2019-06-05T16:02:03.589770Z 0 [Note] [MY-010747] [Server] Plugin 'FEDERATED' is disabled.\n2019-06-05T16:02:03.590719Z 0 [Note] [MY-010733] [Server] Shutting down plugin 'MyISAM'\n2019-06-05T16:02:03.590763Z 0 [Note] [MY-010733] [Server] Shutting down plugin 'CSV'<\/code><\/pre>\n\n\n\n\n\n<p><strong>validate-config<\/strong> is convenient and can be very useful.<br>It may be worthwhile to include it in your upgrade process.<\/p>\n\n\n\n\n\n<h2 class=\"wp-block-heading\">References<\/h2>\n\n\n\n<ul class=\"wp-block-list\"><li><a rel=\"noreferrer noopener\" aria-label=\"Server Configuration Validation (opens in a new tab)\" href=\"https:\/\/dev.mysql.com\/doc\/refman\/8.0\/en\/server-configuration-validation.html\" target=\"_blank\">Server Configuration Validation<\/a> <\/li><li><a rel=\"noreferrer noopener\" aria-label=\"validate-config (opens in a new tab)\" href=\"https:\/\/dev.mysql.com\/doc\/refman\/8.0\/en\/server-options.html#option_mysqld_validate-config\" target=\"_blank\">validate-config<\/a> <\/li><li><a href=\"https:\/\/dev.mysql.com\/doc\/refman\/8.0\/en\/server-system-variables.html#sysvar_log_error_verbosity\" target=\"_blank\" rel=\"noreferrer noopener\" aria-label=\"log_error_verbosity (opens in a new tab)\">log_error_verbosity<\/a> <\/li><\/ul>\n\n\n\n\n\n<div style=\"height:75px\" aria-hidden=\"true\" class=\"wp-block-spacer\"><\/div>\n\n\n\n<p class=\"has-vivid-red-color has-text-color\"><strong>Thanks for using MySQL!<\/strong><\/p>\n\n\n\n<div style=\"height:25px\" aria-hidden=\"true\" class=\"wp-block-spacer\"><\/div>\n\n\n\n<div style=\"height:20px\" aria-hidden=\"true\" class=\"wp-block-spacer\"><\/div>\n\n\n\n<p><a href=\"https:\/\/www.linkedin.com\/groups\/12524512\/\" target=\"_blank\" rel=\"noopener\" title=\"Olivier DASINI on Linkedin\">Follow me on Linkedin<\/a><\/p>\n\n\n\n<p>Watch my videos on my <a href=\"https:\/\/www.youtube.com\/channel\/UC12TulyJsJZHoCmby3Nm3WQ\" target=\"_blank\" rel=\"noreferrer noopener\" title=\"Olivier's MySQL Channel\">YouTube channel<\/a> and <a href=\"https:\/\/www.youtube.com\/channel\/UC12TulyJsJZHoCmby3Nm3WQ\/?sub_confirmation=1\" target=\"_blank\" rel=\"noreferrer noopener\" title=\"Subscribe\">subscribe<\/a>.<\/p>\n\n\n\n<p>My <a href=\"https:\/\/www.slideshare.net\/freshdaz\" target=\"_blank\" rel=\"noreferrer noopener\" title=\"Olivier DASINI on Slideshare\">Slideshare account<\/a>.<\/p>\n\n\n\n<p>My <a href=\"https:\/\/speakerdeck.com\/freshdaz\/\" target=\"_blank\" rel=\"noreferrer noopener\" title=\"Olivier DASINI on Speaker Deck\">Speaker Deck account<\/a>.<\/p>\n\n\n\n<div style=\"height:25px\" aria-hidden=\"true\" class=\"wp-block-spacer\"><\/div>\n\n\n\n<p class=\"has-vivid-red-color has-text-color\"><strong>Thanks for using HeatWave &amp; MySQL!<\/strong><\/p>\n","protected":false},"excerpt":{"rendered":"<p>Since 8.0.16, MySQL Server supports a &#8211;validate-config option that enables the startup configuration to be checked for problems without running the server in normal operational mode.<br \/>\n&#8211;validate-config can be used any time, but is particularly useful after an upgrade, to check whether any options previously used with the older server are considered by the upgraded server to be deprecated or obsolete. <\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"jetpack_post_was_ever_published":false,"footnotes":""},"categories":[88,203],"tags":[935,1210,1211],"class_list":["post-3166","post","type-post","status-publish","format-standard","hentry","category-astuce","category-mysql-en","tag-check","tag-configuration","tag-startup"],"aioseo_notices":[],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/p9LfWW-P4","jetpack-related-posts":[{"id":3086,"url":"https:\/\/dasini.net\/blog\/2019\/05\/21\/mysql-innodb-cluster-howto-2-validate-an-instance\/","url_meta":{"origin":3166,"position":0},"title":"MySQL InnoDB Cluster &#8211; HowTo #2 &#8211; Validate an instance","author":"Olivier DASINI","date":"21 mai 2019","format":false,"excerpt":"Q: Validate an instance for MySQL InnoDB Cluster usage? A: Use check_instance_configuration()","rel":"","context":"Dans &quot;Group Replication&quot;","block_context":{"text":"Group Replication","link":"https:\/\/dasini.net\/blog\/category\/group-replication-en\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":4351,"url":"https:\/\/dasini.net\/blog\/2021\/02\/23\/mysql-recovery\/","url_meta":{"origin":3166,"position":1},"title":"MySQL Recovery","author":"Anastasia Papachristopoulou","date":"23 f\u00e9vrier 2021","format":false,"excerpt":"If you are dealing with data, and you most probably are if you are reading this, one of your biggest fears would be not to be able to retrieve them. In a world where data actually surround us, it is critical to be able to retrieve them fast and with\u2026","rel":"","context":"Dans &quot;MySQL&quot;","block_context":{"text":"MySQL","link":"https:\/\/dasini.net\/blog\/category\/mysql-en\/"},"img":{"alt_text":"","src":"https:\/\/i0.wp.com\/dasini.net\/blog\/wp-content\/uploads\/2021\/02\/IMG-7b894485ba416774df71d5cbbabd964b-V-3.jpg?resize=350%2C200","width":350,"height":200,"srcset":"https:\/\/i0.wp.com\/dasini.net\/blog\/wp-content\/uploads\/2021\/02\/IMG-7b894485ba416774df71d5cbbabd964b-V-3.jpg?resize=350%2C200 1x, https:\/\/i0.wp.com\/dasini.net\/blog\/wp-content\/uploads\/2021\/02\/IMG-7b894485ba416774df71d5cbbabd964b-V-3.jpg?resize=525%2C300 1.5x, https:\/\/i0.wp.com\/dasini.net\/blog\/wp-content\/uploads\/2021\/02\/IMG-7b894485ba416774df71d5cbbabd964b-V-3.jpg?resize=700%2C400 2x, https:\/\/i0.wp.com\/dasini.net\/blog\/wp-content\/uploads\/2021\/02\/IMG-7b894485ba416774df71d5cbbabd964b-V-3.jpg?resize=1050%2C600 3x"},"classes":[]},{"id":3193,"url":"https:\/\/dasini.net\/blog\/2019\/07\/09\/mysql-innodb-cluster-recovering-and-provisioning-with-mysqldump\/","url_meta":{"origin":3166,"position":2},"title":"MySQL InnoDB Cluster &#8211;  Recovering and provisioning with mysqldump","author":"Olivier DASINI","date":"9 juillet 2019","format":false,"excerpt":"As the administrator of a cluster, among other tasks, you should be able to restore failed nodes and grow (or shrink) your cluster by adding (or removing) new nodes. In MySQL, as a backup tool (and if your amount of data is not too big), you can use mysqldump a\u2026","rel":"","context":"Dans &quot;Group Replication&quot;","block_context":{"text":"Group Replication","link":"https:\/\/dasini.net\/blog\/category\/group-replication-en\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":3219,"url":"https:\/\/dasini.net\/blog\/2019\/07\/11\/mysql-innodb-cluster-recovering-and-provisioning-with-mysql-enterprise-backup\/","url_meta":{"origin":3166,"position":3},"title":"MySQL InnoDB Cluster &#8211; Recovering and provisioning with MySQL Enterprise Backup","author":"Olivier DASINI","date":"11 juillet 2019","format":false,"excerpt":"Like I stated in my previous article - MySQL InnoDB Cluster - Recovering and provisioning with mysqldump : \"As the administrator of a cluster, among others tasks, you should be able to restore failed nodes and to add (or remove) new nodes\". Well, I still agree with myself :) MySQL\u2026","rel":"","context":"Dans &quot;Group Replication&quot;","block_context":{"text":"Group Replication","link":"https:\/\/dasini.net\/blog\/category\/group-replication-en\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":4774,"url":"https:\/\/dasini.net\/blog\/2021\/08\/24\/discovering-mysql-database-service-episode-4-dump-your-mysql-data-into-an-object-storage-bucket\/","url_meta":{"origin":3166,"position":4},"title":"Discovering MySQL Database Service &#8211; Episode 4 &#8211; Dump your MySQL data into an Object Storage bucket","author":"Olivier DASINI","date":"24 ao\u00fbt 2021","format":false,"excerpt":"This is the fourth episode of \u201cDiscovering MySQL Database Service\u201c, a series of tutorials where I will show you, step by step, how to use MySQL Database Service and some other Oracle Cloud Infrastructure services. In the previous episode we\u2019ve created our Virtual Cloud Network, that provides you with complete\u2026","rel":"","context":"Dans &quot;Cloud&quot;","block_context":{"text":"Cloud","link":"https:\/\/dasini.net\/blog\/category\/cloud\/"},"img":{"alt_text":"MySQL Database Service","src":"https:\/\/i0.wp.com\/dasini.net\/blog\/wp-content\/uploads\/2021\/06\/MDS_car_801x600.png?resize=350%2C200&ssl=1","width":350,"height":200,"srcset":"https:\/\/i0.wp.com\/dasini.net\/blog\/wp-content\/uploads\/2021\/06\/MDS_car_801x600.png?resize=350%2C200&ssl=1 1x, https:\/\/i0.wp.com\/dasini.net\/blog\/wp-content\/uploads\/2021\/06\/MDS_car_801x600.png?resize=525%2C300&ssl=1 1.5x, https:\/\/i0.wp.com\/dasini.net\/blog\/wp-content\/uploads\/2021\/06\/MDS_car_801x600.png?resize=700%2C400&ssl=1 2x"},"classes":[]},{"id":3058,"url":"https:\/\/dasini.net\/blog\/2019\/05\/14\/check-constraints-in-mysql\/","url_meta":{"origin":3166,"position":5},"title":"CHECK constraints in MySQL","author":"Olivier DASINI","date":"14 mai 2019","format":false,"excerpt":"MySQL (really) supports CHECK CONSTRAINT since version 8.0.16. In this article I will show you 2 things: - An elegant way to simulate check constraint in MySQL 5.7 & 8.0. - How easy & convenient it is to use CHECK constraints in 8.0.16.","rel":"","context":"Dans &quot;Astuce&quot;","block_context":{"text":"Astuce","link":"https:\/\/dasini.net\/blog\/category\/astuce\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]}],"jetpack_likes_enabled":true,"_links":{"self":[{"href":"https:\/\/dasini.net\/blog\/wp-json\/wp\/v2\/posts\/3166","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/dasini.net\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/dasini.net\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/dasini.net\/blog\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/dasini.net\/blog\/wp-json\/wp\/v2\/comments?post=3166"}],"version-history":[{"count":21,"href":"https:\/\/dasini.net\/blog\/wp-json\/wp\/v2\/posts\/3166\/revisions"}],"predecessor-version":[{"id":3192,"href":"https:\/\/dasini.net\/blog\/wp-json\/wp\/v2\/posts\/3166\/revisions\/3192"}],"wp:attachment":[{"href":"https:\/\/dasini.net\/blog\/wp-json\/wp\/v2\/media?parent=3166"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/dasini.net\/blog\/wp-json\/wp\/v2\/categories?post=3166"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/dasini.net\/blog\/wp-json\/wp\/v2\/tags?post=3166"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}