Tag: MariaDB

Categories
Windows

How to see MySQL server status by phpMyAdmin.

同一記事の日本語版
Fig.01 Status Monitor
Fig.01 Status Monitor

   The topic “How to see MySQL log by phpMyAdmin” came up on TODOS・何でも情報交換(Japanese). I checked out this and am going to write what I understood about this.
 
   If you use phpMyAdmin 4.0(maybe) or later, you can audit a status of MySQL servers by phpMyAdmin Monitor. When you log in your phpMyAdmin and go to Status > Monitor, you can reach the graphs. If your server works normal, I think the graphs are enough for you. However, if you need more information, you can refer a slow_query_log or a general_log there.

Fig.02 Disabled
Fig.02 disabled

When you click “Instructions/Setup” at the Monitor page and have the following messages(Also see Fig.02), you have to set xxx_log to ‘ON’ and log_output to ‘TABLE’.
　　　slow_query_log and general_log are disabled.
　　　log_output is not set to TABLE.
 
   If you have enough privileges as a MySQL user, you can set the values by yourself. If not, you have to ask your system administrator. In most case only root users can set the values, if not such server circumstances are very dangerous, I bet.
   Your Server works, then I think you don’t need the general_log of your server but the slow_query_log. If you set them by yourself, you need to log in your MySQL as a root at any rate. If you can log in it as a root…
 
　　To use command line interface:
　　　SET GLOBAL slow_query_log = ON;
　　　SET GLOBAL log_output= TABLE;
 
　　To use phpMyAdmin:
　　　1.Select Variables on Menu with no database.
　　　　Input “slow query log” to the filter form and edit the value to ‘ON’
　　　　Save
　　　2.Input “log output” to the filter form and edit the value to ‘TABLE’
　　　　Save
 
   Log out as the root.

Fig.03 Enabled
Fig.03 Enabled


   Now you can use a “slow_query_log” from the Monitor of phpMyAdmin, when you log in as a root.
 
   By the way, when you restart mysqld, these settings have gone. If you want to stay the settings eternal, you should add the following lines to my.ini/my.cnf’s [mysqld] area.
　　　slow_query_log = ON
　　　log_output = TABLE
 
   As above, you can use a “slow_query_log” from the Monitor of phpMyAdmin as a root. However, this is less convenient. Don’t you think you’ll safely use it by a WordPress MySQL account? I think so, too. I asked くりくりさん on TODOS・何でも情報交換 “What privileges should I give a normal user who can use this feature?” He told me “It’s OK only for the database”.
   The slow_log table exists on the mysql database. So I did the following command.
 
　　　GRANT SELECT (lock_time, start_time, rows_examined, db, rows_sent, query_time, sql_text, user_host) ON mysql.slow_log TO ‘WP-user’@’localhost’;
 
   This is very limited privileges, so I think it’s acceptable.
 
   At this time, my WordPress MySQL account has the following privileges.
————
GRANT USAGE ON *.* TO ‘WP-user’@’localhost’ IDENTIFIED BY ‘passphrase’;
GRANT ALL PRIVILEGES ON WPdatabase.* TO ‘WP-user’@’localhost’;
GRANT SELECT (lock_time, start_time, rows_examined, db, rows_sent, query_time, sql_text, user_host) ON mysql.slow_log TO ‘WP-user’@’localhost’;
————

Categories
Windows

Updating to MariaDB 10.0.11.

同一記事の日本語版

   I’ve updated to MariaDB 10.0.11 on my server (Server OS : Windows7HP+SP1(x86)). I write the procedures as follows.

   First, I backed up all the sever data. Especially, MariaDB and MyDB.

   Next, I updated to MariaDB 10.0.11.

  1. Downloaded mariadb-10.0.11-win32.zip.
  2. Extracted the Zip archive.
  3. Control Panel >> Administrative tools >> Services
    Select the MariaDB service name and stop.
  4. Delete all contents in the MariaDB folder. Install the four folders named bin, include, lib and share and license files to the folder.
  5. Control Panel >> Administrative tools >> Services
    Select the MariaDB service name and start.

   That’s it.

   I used this opportunity to update to phpMyAdmin 4.2.3 and this is its ChangeLog. If you need its configuration, see “phpMyAdmin 4.2.0 is released”.

Categories
Windows

Upgrading from MariaDB 5.5 to 10 on Windows.

同一記事の日本語版
Update information      Edit(Jun.30)

   Now we have the first stable version of MariaDB 10.0 Series which was released on Mar 31. Today, I’ve upgraded from MariaDB 5.5 to 10 on Windows7HP+SP1(x86). Though this is not an update but an upgrade, the procedures are nothing different from the last update.

   First, I backed up all the sever data. Especially, MariaDB and MyDB.

   Next, I upgraded to MariaDB 10.0.10.

  1. Downloaded mariadb-10.0.10-win32.zip.
  2. Extracted the Zip archive.
  3. Control Panel >> Administrative tools >> Services
    Select the MyDB service name and stop.
  4. Delete all contents in the MariaDB folder. Install the four folders named bin, include, lib and share and license files to the folder.
  5. Control Panel >> Administrative tools >> Services
    Select the MyDB service name and start.

   That’s it.

Edit(Jun.30):
   I forgot to write. After upgrading, you have the error message “Please use mysql_upgrade to fix this error.” on the error log, so you need “mysql_upgrade“.

Categories
Windows

Updating to MariaDB 5.5.36.

同一記事の日本語版

   Today, I updated to MariaDB 5.5.36 on Windows7HP+SP1(x86). Here is its Changelog.

   First, I backed up all the sever data. Especially, MariaDB and MyDB.

   Next, I updated to MariaDB 5.5.36.

  1. Downloaded mariadb-5.5.36-win32.zip.
  2. Extracted the Zip archive.
  3. Control Panel >> Administrative tools >> Services
    Select the MyDB service name and stop.
  4. Replace old four folders with new ones. The folders exist in MariaDB and their names are bin, include, lib and share.
  5. Control Panel >> Administrative tools >> Services
    Select the MyDB service name and start.

   That’s it.

Categories
Windows

Moving to MariaDB5.5.

同一記事の日本語版
Update information      Edit(Dec.21)    Edit2(Dec.25)    Edit3(2014.Jun.22)

MaintenanceNotice   Yesterday, I worked very hard. For what? Well, moving to MariaDB5.5 from MySQL on Windows7HP+SP1(x86). Haha.

   First, I backed up all the sever data.
   Next, I made a maintenance.html like the right, and for maintenance I added the next lines to the head of my .htaccess at the Document Root. The text in it is like the below. (refer to: mod_rewrite, <IfModule>)

　　　　　ErrorDocument 503 /maintenance.html

　　　　　RewriteEngine On
　　　　　RewriteCond %{REQUEST_URI} !=/maintenance.html
　　　　　RewriteCond %{REMOTE_ADDR} !=IP address for Admin
　　　　　RewriteRule ^.*$ – [R=503,L]

　　　　　Header set Retry-After “Wed, 18 Dec 2013 01:00:00 GMT”

   On the page, I found “This section should only be used if you need to have one configuration file that works whether or not a specific module is available. In normal operation, directives need not be placed in <IfModule> sections.”. So, I thought I did not need <IfModule> sections.

   Then, I announced the server maintenance on my sites and began moving to MariaDB5.5.

   I had a clean installation of MariaDB because I wanted to change my sql engine from MyISAM to InnoDB. When I started using MySQL, I made the tables by MyISAM. Recently, I heard about InnoDB merits several times. So I always wanted to move to InnoDB, but I also found someone was in troubles on moving to it on the Internet. Hence I have hesitated to make a move because I can NOT handle them if something wrong happens despite my poor knowledge about the sql.

   MariaDB has InnoDB as its default. So I was going to recreate all my tables on this occasion if necessary.

Step1 The uninstallation of MySQL.

  1. Deactivated all WordPress plugins on my sites.
  2. Backed all databases up separately from the sever data backup.
  3. Also exported all contents of my WordPress from the site Dashboard. Because I was going to import all contents by the WordPress Importer if possible. I gave it up as described below, though.
  4. Stop the service.
    Control Panel >> Administrative tools >> Services
    Select the MySQL service name and stop.
  5. Delete the service.
    Run a cmd.exe as an Administrator.
    > sc delete MySql
  6. Removed the folders, MySQL and MyDATA (<--- These are MySQL scripts and data on my server).

Step2 The installation of MariaDB.

  1. Downloaded mariadb-5.5.34-win32.zip from MariaDB.
  2. Running my eyes overInstalling MariaDB Windows ZIP packages, I went to the page about mysql_install_db.exe.
  3. Extracted the Zip archive. Made two folders named MariaDB and MyDB on my server ware partition named Drive_SV. Installed all things made by extract to the folder MariaDB.

    Run a cmd.exe as an Administrator.
    > cd Drive_SV:MariaDBbin
    > mysql_install_db.exe –datadir=Drive_SV:MyDB –service=MyDB –password=secret

    By this, I was able to set the password for the root user and had a new my.ini in the MyDB.

  4. Control Panel >> Administrative tools >> Services
    Select the MyDB service name and start
    If its ‘Startup Type’ is not ‘Automatic’, you should change it to ‘Automatic’.

Step3 Access MariaDB via phpMyAdmin.

  1. Accessed MyDB as the root user from phpMyAdmin.
    Imported one of my backup database, phpmyadmin.
  2. Made a WordPress User and gave it all WordPress database privileges except Grant and no Global privileges. Of course set a password for it. Made a database for the WordPress. Their collation is utf8_general_ci.
    Logout.

   Import by WordPress Importer and I gave it up. The reason is the below.

   After a new WordPress installation, I imported all contents by WordPress Importer. But unfortunately, I found the fact that the plugin neglected some tags like <object>, it was inconvenient for me. I don’t know it neglects what kind tags and to examine them by myself is too much trouble. Therefore, I gave up this method.

Step4 Restored all WordPress database via phpMyAdmin.

  1. I wanted to use the InnoDB, so I replaced all ‘ENGINE=MyISAM’ by ‘ENGINE=InnoDB’ in the backup sql file.
  2. Login as the WordPress User.
    Exported the current WordPress database.
    Dropped all tables on the WordPress table because my backup sql file contained all data.
  3. Imported the backup. I had an error like this.
    　　　　　#1214 – The used table type doesn’t support FULLTEXT indexes

    The backup file was originally MyISAM, so it includes FULLTEXT indexes. Actually it uses by YARPP as keys of post_title and post_content. Hummm. But on the forum the plugin author says we can use YARPP on the InnoDB though its performance slows down.

    I removed all lines about FULLTEXT indexes in the file. (I remember I heard we can use FULLTEXT with InnoDB on MySQL5.6.–Dec.25Edit）

  4. Dropped all tables again.

    Imported the customized file. I had another error.
    　　　　　#1064 – You have an error in your SQL syntax;

    This error was my fault. When I removed FULLTEXT indexes I forgot to remove a “,” like this.
    　　　　　KEY `post_author` (`post_author`),　　　<<--------This is the ',' I forgot to remove. 　　　　　) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=xxxx ; I removed all such ','s.

  5. Dropped all tables again.

    Imported the customized file. Complete.

Step5 Back to normal condition.

  1. Login the WordPess.
    Activated all plugins.
    Checked all script behaviors.

    Change .htaccess text to end the maintenance.

  2. Actually, I still have an error about Jetpack on my parent site. Like this.

    　　　　　Your website needs to be publicly accessible to use Jetpack: site_inaccessible
    　　　　　Error Details: The Jetpack server was unable to communicate with your site https://MySITE
    　　　　　[IXR -32300: transport error: http_request_failed SSL certificate problem: self signed
    　　　　　certificate in certificate chain]

    But I think this is not the maintenance faults. Now I am waiting for a reply on the Jetpack forum.

   Now I use MariaDB5.5. Clap, clap.

Edit(Dec.21):
   After I changed SQL Storage Engine from MyISAM to InnoDB, the plugin YARPP performance slowed down very much. It was more than my expecting. So, I decided to rollback the Engine about the table wp_posts by YARPP instruction message.

  1. Login phpMyAdmin.
  2. Select the database for WordPress.
  3. Select the table wp_posts.
  4. Select ‘Operations’ from the top navigation bar.
  5. Change Storage Engine from Innodb to MyISAM at Table options.
  6. Click Go button of Tabble options.
  7. Logout phpMyAdmin.

   But YARPP didn’t recognize this change, though the author have a specialized feature for this. I went to the YARPP support forum to find a solution. I found MyISAM Override check doesn’t work. I followed hussong‘s instructions.

  1. Deactivate the plugin.
  2. Login phpMyAdmin.
  3. Select the database for WordPress.
  4. Select the table wp_options.
  5. Select ‘SQL’ from the top navigation bar.
  6. Use SELECT * FROM `wp_options` WHERE option_name LIKE "yarpp%"
  7. Delete all I found.You can see yarpp_fulltext_disabled = 1. Change it to yarpp_fulltext_disabled = 0
  8. Logout phpMyAdmin.
  9. Activate the plugin.
  10. Setting the plugin again because all old settings gone.

Now, I can use Titles and Bodies consider options. Happy!

Edit2(Dec.25):
   I wrote “About Jetpack trouble“.

Edit3(2014.Jun.22):
   I wrote an article The solution of “SSL3_READ_BYTES:sslv3 alert handshake failure” on WordPress.