• Home
  • Logos (λόγος)
  • Haaretz (הארץ)
    • Recipes
    • 魚
  • Kendo (剣道)
  • Development
  • Wiki
  • 韓承佑

Sojourning Dinosaur

Veritas omnia vincit 信使可覆

mysql

Introduction to basic commands in PostgreSQL for MySQL users

August 29, 2016 by mhan Leave a Comment

Last tested on Ubuntu 16.04.01 LTS (xenial)

Getting into DB console.

MySQL:

$ mysql -uroot -p

PostgreSQL:

$ sudo su postgres
postgres@hydrogen:~$ psql

Creating DB

Creating a database and granting a user complete access.

MySQL:

mysql> create database mydb;
mysql> grant all on mydb.* to dbuser@localhost identified by 'mypass';

PostgreSQL:

postgres=# create user dbuser with password 'mypass';
postgres=# create database mydb;
postgres=# grant all privileges on database mydb to dbuser;

Listing DBs

You can list the DBs.

MySQL:

mysql> show databases;

PostgreSQL:

postgres=# \l

Selecting a DB

You can select a DB.

MySQL:

mysql> use mydb;

PostgreSQL:

postgres=# \connect mydb;

After selecting a DB you can go ahead and execute SQL commands.

Listing all tables

You can list tables in a DB.

MySQL:

mysql> show tables;

PostgreSQL:

postgres=# \dt

Change the password of a user

You can list tables in a DB.

MySQL:

mysql> set password for 'dbuser'@'localhost' = password('newpassword');

PostgreSQL:

postgres=# alter user "dbuser" with password 'newpassword';

Exiting from DB

Ctrl-D should exit from both.

Posted in: System administration Tagged: mysql, postgresql

Copying or moving all of MySQL databases

August 25, 2016 by mhan Leave a Comment

This is just one of the ways you can move all of the MySQL databases from one server to another.  This was tested on Ubuntu 16.04.01 LTS (xenial) distro.

Log in as an admin on MySQL Console and lock the database to allow only read operations.

mysql> flush tables with read lock;
mysql> set global read_only = on;
mysql> exit

Dump all of the databases into a file.

$ mysqldump --lock-all-tables -u root -p --all-databases > dbs.sql

Copy the dump to the new server. RSYNC is preferred over SCP, especially if the file is large.

$ rsync -tvz --progress dbs.sql mhan@newserver.com:~/files/
or
$ scp dbs.sql mhan@newserver.com:~/files/

The DB can be (optionally) unlocked. This may or may not be a good thing to do in your case. Do it at your own risk.

mysql> set global read_only = off;
mysql> unlock tables;
mysql> exit

On the new server, execute this command to import the new SQL dump.

$ mysql -u root -p < ~/files/dbs.sql

IMPORTANT: If your file is large, or you just have a lot of records, you may want to make sure you have something bigger than 16M for max_allowed_packet attribute in your my.cnf (usually found under /etc/mysql/ or /etc/mysql/mysql.conf.d/) on your new server where you’re doing the import, else the server could hang on a large insert operation and your MySQL server may actually decide to go away, literally.  On one of the servers I had it for 1024M just for this operation and brought it back low afterwards.

Posted in: System administration Tagged: mysql, sysadmin

Moving all of the databases from one server to another

August 17, 2016 by mhan Leave a Comment

Log in as an admin on MySQL Console and lock the database to allow only read operations.

mysql> flush tables with read lock;
mysql> set global read_only = on;
mysql> exit

Dump all of the databases into a file.

$ mysqldump --lock-all-tables -u root -p --all-databases > dbs.sql

Copy the dump to the new server. RSYNC is preferred over SCP, especially if the file is large.

$ rsync -tvz --progress dbs.sql mhan@newserver.com:~/files/
or
$ scp dbs.sql mhan@newserver.com:~/files/

The DB can be (optionally) unlocked. This may or may not be a good thing to do in your case. Do it at your own risk.

mysql> set global read_only = off;
mysql> unlock tables;
mysql> exit

On the new server, execute this command to import the new dumped SQL file.

$ mysql -u root -p < ~/files/dbs.sql

https://wiki.michaelhan.net/MySQL

Posted in: Development Tagged: development, mysql, sysadmin
Visit Us On TwitterVisit Us On FacebookVisit Us On GooglePlusVisit Us On Linkedin

TEMPUS FUGIT

February 2019
S M T W T F S
« Jan    
 12
3456789
10111213141516
17181920212223
2425262728  

Recent Posts

  • 에프킬라 맞은 모기처럼
  • True significance
  • Our deepest need
  • Identifying with Christ
  • Interesting trends in Christianity over many decades

Recent Comments

    Archives

    • February 2019
    • January 2019
    • October 2018
    • September 2018
    • August 2018
    • July 2018
    • May 2018
    • April 2018
    • March 2018
    • February 2018
    • January 2018
    • December 2017
    • October 2017
    • September 2017
    • August 2017
    • July 2017
    • June 2017
    • May 2017
    • February 2017
    • January 2017
    • October 2016
    • September 2016
    • August 2016
    • July 2016

    Categories

    • AI
    • Development
    • Haaretz (הארץ)
    • Kendo (剣道)
    • Logos (λόγος)
    • Recipes
    • System administration

    Copyright © 2016 ~ 2019 Michael Han