Uly.me

cloud engineer

sql

GCP List of SQL Instances

Here’s how to list SQL instances within your GCP project.

gcloud sql instances list --project your-project-id

Output:

NAME             DATABASE_VERSION         LOCATION       TIER              PRIMARY_ADDRESS  PRIVATE_ADDRESS  STATUS
database1        SQLSERVER_2017_STANDARD  us-central1-a  db-n1-standard-1  -                10.10.10.11    RUNNABLE
database2        SQLSERVER_2017_STANDARD  us-central1-c  db-n1-standard-1  -                10.10.10.12    RUNNABLE

MySQL Restore

Here’s how to restore a MySQL database from mysqldump.

mysql -u user -p
mysql> drop database databasename;
mysql> quit;
Bye
mysql -u user -p databasename < filename.sql

Drop database first, then import the SQL file.

Run SQL in Bash

Here’s an example on how to run MySQL commands from Bash.

mysql dbname -e "UPDATE wp_options SET option_value='http://domain.com' WHERE option_name='home'";
mysql dbname -e "UPDATE wp_options SET option_value='http://domain.com' WHERE option_name='siteurl'";

The example updates WordPress’ home and siteurl. Script contains no password. It’s using .my.cnf config file.

Using Like in SQL

I was looking at my SQL statements the other day. I was doing a bunch of comparisons with a certain field that contain a pattern of words. My original SQL was quite tedious as displayed below. After much thought, I was able to pare it down to something much simpler. The newer SQL statement is much shorter and to the point. It uses the operator LIKE to search for a pattern.

OLD SQL

SELECT 
  * 
FROM 
  mytable 
WHERE 
( product="model-1" OR
  product="model-2" OR 
  product="model-3" OR
  product="model-4" OR
  product="model-5" OR
  product="model-6" ) AND
  id=105

NEW SQL

SELECT 
  * 
FROM 
  mytable 
WHERE 
  product LIKE "%model%" AND
  id=105

The newer SQL is a vast improvement over the previous one. I was also forced to use parenthesis around the previous SQL statement to group together the OR comparisons. Without the parenthesis, my results were inaccurate because the AND had precedence over the OR. With an improved SQL, there’s no need to use parenthesis. Obviously the LIKE operator only works if there’s a pattern. I use % to make the search more liberal, otherwise it would look for an exact match.

Importing SQL in phpMyAdmin

If you have a problem importing SQL files into phpMyAdmin, the issue could be that the upload limit in PHP is set too low. By default, PHP sets the upload file limit to 2MB, which is too low for most people. You can increase the limit to something more realistic such as 16MB, if you’re working mostly with medium-sized SQL databases.

If you have access to the Terminal and have root access, you can edit your PHP.ini settings. First, you need to find out where your default PHP.ini is located. There might be several PHP.ini files in your system, but there’s only one bound to Apache. To determine to which one is being used, I suggest you create a file that contains the PHP function called phpinfo(). Place this file your web server and run it.

Create a file called phpinfo.php. Enter the code below. Save. Upload to server.

<?php phpinfo(); ?>

Now, open your browser and point the URL where your phpinfo.php is stored on your web server. The phpinfo.php file will run the phpinfo() function and will display the environment variables being used by Apache. Near the top of the page, you’ll see the path of the php.ini. Now that you know where your php.ini file is exactly located, you’ll need to edit that file and look for the Upload Limits.

In Ubuntu, my php.ini is located in /etc/php5/apache2/php.ini.

sudo nano /etc/php5/apache2/php.ini

Look for the File Uploads text as displayed below. Change from 2M to 16M. Save file.

;;;;;;;;;;;;;;;;
; File Uploads ;
;;;;;;;;;;;;;;;;
 
upload_max_filesize = 16M

Finally, you need to reboot Apache for your changes take effect.

sudo /etc/init.d/apache restart