{"id":1,"date":"2015-08-27T08:36:41","date_gmt":"2015-08-27T08:36:41","guid":{"rendered":"http:\/\/small-service.com\/?p=1"},"modified":"2018-07-14T13:49:46","modified_gmt":"2018-07-14T10:49:46","slug":"hello-world","status":"publish","type":"post","link":"https:\/\/small-service.com\/?p=1","title":{"rendered":"Mysql useful commands"},"content":{"rendered":"<h4>Here will be some commands and their examples with explanations.<\/h4>\n<p><!--more--><\/p>\n<ul>\n<li><strong>create DB:<\/strong><br \/>\n<code>CREATE DATABASE `my_db` CHARACTER SET utf8 COLLATE utf8_general_ci;<\/code><\/li>\n<li><strong>show db collection and charset:<\/strong><br \/>\n<code>USE db_name;<br \/>\nSELECT @@character_set_database, @@collation_database;<\/code><\/li>\n<li><strong>create user:<\/strong><br \/>\n<code>CREATE USER 'jeffrey'@'localhost' IDENTIFIED BY 'mypass';<\/code><\/li>\n<li><strong>grant permissions to some db:<\/strong><br \/>\n<code>GRANT ALL PRIVILEGES ON 'DB_name'.* TO 'monty'@'localhost';<\/code><\/li>\n<li><strong>show processlist (who&#8217;s connected and if they&#8217;re currently running anything):<\/strong><br \/>\n<code>SELECT * FROM information_schema.processlist \\G<br \/>\nshow processlist;<\/code><\/li>\n<li><strong>enable queue loggining:<\/strong><br \/>\n<code>SET global log_output='table';<br \/>\nSET global general_log=1;<\/code><\/li>\n<li><strong>enable\u00a0slow logs:<\/strong><br \/>\n<code>SET global long_query_time=2;<br \/>\nSET global slow_query_log=1;<\/code><\/li>\n<li><strong>mysql skipp counter and restart replication;<\/strong><br \/>\n<code>STOP slave;<br \/>\nSET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;<br \/>\nSTART slave;<br \/>\nshow slave status \\G<\/code><\/li>\n<li><strong>enable mysql profiling:<\/strong><br \/>\n<code>set profiling=1;<br \/>\nquery;<br \/>\nshow profiles;<br \/>\nshow profile for query qry_number;<br \/>\nset profiling=0;<\/code><\/li>\n<li><strong>skip specific error in replication. Add below to my.cnf:<\/strong><br \/>\n<code>slave_skip_errors error_number<\/code><\/li>\n<li><strong>log-slave-updates<\/strong><br \/>\n<code>innodb_flush_log_at_trx_commit=2 (default = 1)<br \/>\ninnodb_flush_method=O_DIRECT<\/code><\/li>\n<li><strong>create mysql replication:<\/strong><br \/>\n<code>GRANT replication slave ON *.* TO \"repl_user\"@\"%\" IDENTIFIED BY \"password\";<br \/>\nCHANGE MASTER TO MASTER_HOST='master_host_fqdn', master_user='repl_user', MASTER_PASSWORD='password', MASTER_LOG_FILE='binlog_file_name', MASTER_LOG_POS=pos_number;<\/code><\/li>\n<li><strong>Enable read-only mode:<\/strong><br \/>\n<code>SET GLOBAL read_only = ON;<\/code><br \/>\nor add to \/etc\/mysql\/my.cnf: <code>read_only=1<\/code><\/li>\n<li><strong>change column type in table:<\/strong><br \/>\n<code>ALTER TABLE table_name MODIFY column_name type;<\/code>\n<\/li>\n<li><strong>add column to table:<\/strong><br \/>\n<code>ALTER TABLE table_name ADD column_name type;<\/code>\n<\/li>\n<li><strong>Change user password:<\/strong><br \/>\n<code>ALTER USER some_user@'%' IDENTIFIED BY 'new_password';<\/code>\n<\/li>\n<li><strong>compare databases between servers (also available for the same server):<\/strong><br \/>\n<code>mysqldbcompare  --server1=root@localhost:3306 --server2=root:pwd@mysql_url:3306 --changes-for=server2<\/code><br \/>\n &#8211;changes-for &#8211; means show changes for server. I.e. above should be compared with server1\n<\/ul>\n","protected":false},"excerpt":{"rendered":"<p>Here will be some commands and their examples with explanations.<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":[],"categories":[15],"tags":[20,4,17,18,19],"_links":{"self":[{"href":"https:\/\/small-service.com\/index.php?rest_route=\/wp\/v2\/posts\/1"}],"collection":[{"href":"https:\/\/small-service.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/small-service.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/small-service.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/small-service.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=1"}],"version-history":[{"count":10,"href":"https:\/\/small-service.com\/index.php?rest_route=\/wp\/v2\/posts\/1\/revisions"}],"predecessor-version":[{"id":275,"href":"https:\/\/small-service.com\/index.php?rest_route=\/wp\/v2\/posts\/1\/revisions\/275"}],"wp:attachment":[{"href":"https:\/\/small-service.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=1"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/small-service.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=1"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/small-service.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=1"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}