Tag Archives: grant

One-liner to copy remote MySQL database to local computer

Posted on by
5

The following commands dump a MySQL database from a remote server and create a corresponding database on the local computer.

The instructions have been tested on OSX and Linux (Ubuntu/Debian). On-line SSH compression is applied, so transferring SQL files, which are text content and compress well, should be around 6x faster than normal.

(Well… actually the script is six lines, but because this is my blog I’ll decide it doesn’t count)

The script

  • Remotely runs mysqldump and puts the result to a local file
  • Creates a MySQL database and corresponding user with full access to this database
  • Reads the content of mysqldump to the newly created database
 ssh user@dserver.com -C -o CompressionLevel=9 mysqldump -u YOURDATABASEUSER --password=YOURDATABASEPASSWORD --skip-lock-tables --add-drop-table YOURDATABASENAME > YOURDATABASENAME.sql
mysql -uroot -p
create database YOURDATABASENAME;
connect YOURDATABASENAME;
source YOURDATABASENAME.sql
GRANT ALL ON YOURDATABASENAME.* TO 'YOURDATABASEUSER'@'localhost' identified by 'YOURDATABASEPASSWORD';

Leave out create database and GRANT for the subsequent runs – all data on the local computer will be replaced.

\"\" Subscribe to RSS feed Follow me on Twitter Follow me on Facebook Follow me Google+

Creating MySQL database and user from command line

Posted on by
1

This serves as my own notes – I always forget the syntax.

Prerequisitements

  • How to deal with UNIX command line programs

Use MySQL command to login to running MySQL instance. If this is on local host you can try

mysql -uroot # no password

or

mysql -uroot -p # asks for password

Use -H option to connect to external MySQL host (on shared hosting).

Then create a new database using CREATE DATABASE command. Here we create a database called joomla.

mysql> create database joomla;


Query OK, 1 row affected (0.00 sec)

And then we create a MySQL user whose access is limited to this database only. In this case we create admin user with admin password. Note that MySQL accepts local connections only by default, so MySQL or this user is not exposed to Internet (and potential attacks).

Username is joomla and password is joomladbpassword.

GRANT ALL ON joomla.* TO 'joomla'@'localhost' identified by 'joomladbpassword';

… and now you can proceed to install Joomla.

\"\" Subscribe to RSS feed Follow me on Twitter Follow me on Facebook Follow me Google+