Run multiple MySQL/MariaDB instances on Ubuntu server
We are using MariaDB on an Ubuntu server powered by Webmin and Virtualmin. The MariaDB server has been installed after removing the default MySQL installation that comes with Virtualmin.
Tag mysql
A PHP PDO mysql helper class used in SlimPHP applications
This is a very simple example on how to use SlimPHP and PDO in a basic API that updates, lists and deletes entries from a table. If you are new to building APIs with SlimPHP, then this is a good…
Mysql: Export select rows to CSV on server
Use the following command to export the query rows to a file on the server in the CSV format.
MariaDB – allow user to connect from anyhost
Use the following command in your server to allow user to connect from anyhost: RENAME USER ‘root’@’localhost’ TO ‘root’@’%’; FLUSH PRIVILEGES;
Enable access to MySQL/MariaDB from any host in Linux
Commands: sudo nano Look for [mysqld] and bind-address Edit the new address that matches your desired ip address (0.0.0.0 for all) sudo service mysql restart mysql –uremoteuser -premoteuserpassword GRANT ALL ON *.* TO remoteuser@’%’ IDENTIFIED BY ‘remoteuserpassword’; FLUSH PRIVILEGES;…
Apache 2, PHP 5 and MariaDB on Raspbian
A great tutorial to do so can be found here: Please remember to prefix the commands with sudo or use an elevated privileges user. A nice touch not found in other tutorials is the recommendation to install APCu PHP…
Updating the URL of a WordPress website with SQL commands
Use the following commands to change the URL of a WordPress website directly inside the mysql database: UPDATE wp_options SET option_value = replace(option_value, ”, ”) WHERE option_name = ‘home’ OR option_name = ‘siteurl’; UPDATE wp_posts SET guid = replace(guid, ”,”);…
MySQL JOINs with examples
This is an interesting tutorial on MySQL Joins:
WordPress – db_term_taxonomy reset count
Reset the term count: UPDATE wp_term_taxonomy tt SET count = (SELECT count(p.ID) FROM wp_term_relationships tr LEFT JOIN wp_posts p ON (p.ID = tr.object_id AND p.post_type = ‘post’ AND p.post_status = ‘publish’) WHERE tr.term_taxonomy_id = tt.term_taxonomy_id) WHERE tt.taxonomy = ‘tag’;
“InnoDB: Fatal error: cannot allocate memory for the buffer pool”
The information here helped. Reducing the buffer pool size was not enough. In the next step I activated the swap.
WordPress – delete all media files
To delete the media files without leaving traces in the database, do the following: Connect to the mysql database – using phpmyadmin, for example – and run the following queries: DELETE FROM wp_postmeta WHERE post_id IN ( SELECT id FROM…
Set internal encoding to UTF-8 for php
Use the following code in your script to set the internal php encoding to UTF-8: /* Set internal character encoding to UTF-8 */ mb_internal_encoding(“UTF-8”); mb_http_output( “UTF-8” ); ob_start(“mb_output_handler”); Also, for mysql and postgres you should take into consideration the following…