• Skip to main content

Uly.me

cloud engineer

  • Home
  • About
  • Archives

sql

GCP List of SQL Instances

February 28, 2021

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

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

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

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

Filed Under: Cloud Tagged With: gcloud, gcp, instances, list, sql

MySQL Restore

April 26, 2020

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

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.

Filed Under: Linux Tagged With: import, mysql, restore, sql

Run SQL in Bash

March 10, 2019

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'";

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.

Filed Under: Linux, WP Tagged With: .my.cnf, bash, mysql, sql, wordpress

Using Like in SQL

September 11, 2015

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

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

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.

Filed Under: PHP Tagged With: comparison, like, sql

Importing SQL in phpMyAdmin

February 18, 2014

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(); ?>

<?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

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

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

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

sudo /etc/init.d/apache restart

sudo /etc/init.d/apache restart

Filed Under: PHP Tagged With: phpmyadmin, sql, upload

  • Home
  • About
  • Archives

Copyright © 2023