Tag: mysql

Posted on

Importing a MySQL database with a new name where the original still exists

So you want to export your MySQL database then import it on the same server with a new name.   I tried this several times and using the SOURCE command inside MySQL or just mysql -p < db.sql both resulted in the database re-importing over the original name.   To fix that run: 
sed -i 's/OLDDATABASENAME/NEWDBNAME/g' mysqldumpFile.sql
Then run:   
mysql -p

create NEWDBNAME;

use NEWDBNAME;

SOURCE /path/to/sql/file
  No need for a semicolon on the last line.   Then check the success by:   
show tables;
   
Posted on

MySQL: How to create a database

  To create a simple database (e.g. if you need to create one for a WordPress or Wiki install) use the following after logging in to MySQL as the root user:  
create database [nameOfDatabase];
  Don’t forget the ; at the end, or you’ll end up on a new line consisting of a > prompt waiting for more input. You can put the ; in then if you forgot to complete the command. An example of the above would be:  
create database wikiDB;
  …which would create the database named wikiDB. You should see the following on a successful command:
  Query OK, 1 row affected (0.04 sec)
          If that is all you had to do you can now exit MySQL by typing:  
exit
  …and you’re done!
Posted on https://www.sotechdesign.com.au/asian-singles-melbourne/

How to change a user’s password in Mediawiki

If you have a wiki you may need to change a user’s password from time to time; you can do this from the back end quite easily. First, access mysql:  
mysql -u root -p
  Log in using your root password. Next, list your databases:  
show databases;
  On our test system this shows all of our databases like so:  
mysql> show databases; +——————–+ | Database +——————–+ | information_schema | mysql | performance_schema | press | test | wiki +——————–+ 6 rows in set (0.10 sec)
Select your wiki’s database:  
USE wiki;
  Replace “wiki” in the above with your own database’s name.  
UPDATE user SET user_password = MD5(CONCAT(user_id, ‘-‘, MD5(‘newpasswordgoeshere’))) WHERE user_name = ‘usernameofuser’;  
If this is successful you should get the following:  
Query OK, 1 row affected (0.01 sec) Rows matched: 1  Changed: 1  Warnings: 0
  If something has gone wrong (e.g. a non-existent username) you will get the following instead:  
Query OK, 0 rows affected (0.03 sec) Rows matched: 0  Changed: 0  Warnings: 0
  All done! To leave mysql just type “exit”.  
Posted on

How To: Export all mysql databases for backup

This is a handy command for anyone using multiple mysql databases – it produces a single file which you can easily back up to elsewhere.  
mysqldump -u root -p –all-databases > databasesBackup.sql
  Note the two hyphens before “all”. This command creates the file databasesBackup.sql which contains the contents of all of your databases. This file can be easily rsync’d or scp’d elsewhere to create an offsite backup of your site’s databases.