Razvan Pandor

Had to copy a large DB into a new one…Since MySQL Administrator and PHPMyAdmin failed to backup the 3.7Gb Database I was forced to do it from Shell like this:

mysqldump -u user -p db-name > output_file.out
 /*- launch this from the shell as root with the credentials of the source DB*/

mysql -u user -p new_db-name < output_file.out
/* - this will copy from the dump file into the new database.
you can also download the resulting file and use it on
another server - you could do this the old fashion way by
 copying it on your local machine and then upload it to your
 new server and launch the previous command or if you have "scp":*/

scp output_file.out user@remote_ftp_path

If you wish to select unique combinations of columns on a MySql table you should use GROUP BY like this:

CREATE TABLE dummyTable
SELECT Item1, Item2, Item3, Item4
FROM dummyTable
GROUP BY Item1, Item2, Item3, Item4

this will output the unique combinations of the 4 columns on the dummyTable table.

1. Login with your mysq root user acount:

 $ mysql -h localhost -u root_user -p  - will prompt for the password

- to see a list of existing databases on the server use :

mysql> show databases;  - List all databases in MySQL.

+———–+
| Database |
+ ———–+
|    mysql    |
|     test        |
+  ———- +

2. Create a new database

$ mysqladmin -h localhost -u root_user -p password_of_root_user create new_database

or

mysql> CREATE DATABASE new_database; 

this above will create the database called “new_database” on the server.

3. Add new user for a database

mysql> use new_database; 

– select the target database

mysql> CREATE USER 'test_user' IDENTIFIED BY  'test_pass;              - 

will create a new user that is able to connect to the database from any location ( to limit the connection to ‘localhost’ (or any other location)  use ‘test_user’‘@’localhost’

 mysql> GRANT SELECT,INSERT,UPDATE,CREATE,DROP ON *.* TO  'test_user'

‘;        -set up user privileges

mysql> GRANT ALL ON *.* TO 'test_user';              

– set up all privileges for selected user

mysql> FLUSH PRIVILEGES;                     

  -- Required each time one makes a change to the GRANT table