Create MySQL database and user (including MYSQLdump)


There are loads of ways to do this, Webmin, PHPmyadmin, MySQL GUI tools but if all you have is a Linux command line then you need the good old fashioned native MySQL commands

You will need mysql installed for this to work

yum install mysql
mysql -u (root user of database) -p -h (server name of MySQL database)
eg. mysql -u mysqlroot -p -h localhost

Once you have a command line then you need to create the user, you can also create the database whilst you are here

Create the database

create database (database name);

Now create the user

create user 'username'@'localhost' identified by 'password';
create user 'username'@'%' identified by 'password';
grant all on databasename.* to 'username'@'localhost' with grant option;
grant all on databasename.* to 'username'@'%' with grant option;

To create a mysql dump there are a few options, output to a sql file that then can be user to create the database or straight to the remote data base. Cloning to a remote database can be found in the Duplicate Sugar CRM instance

To create a sql file then

mysqldump -u (root name of mysql) --password=the password 
-h (host name of the server) datbase_name > sqlfilename.sql

You can then link to the database that you wish to run the sql file into by using the following command.

mysql -u (root name of mysql) -p -h (host name of the server) < sqlfilename.sql

Bulk loading into MySQL

Mysql bulk loadint is pretty cool to, the reference for this can be found here, as always though I just want the bit to get t working.


I am using Linux Centos 5.6 mysql version ?????

The text file needs to be stored in the schema directory, in this example I am uploading to the pentaho schema and therefore my path is /u10/mysql/pentaho/

The command is therefore as follows

 mysql -e schema_name -e “load data infile ‘name_of _file_to_import‘ into mysql_table fields terminated by ‘delimiter‘ ingnore number of lines lines”

The ignore number of lines is only needed if there is a header etc…



 mysql pentaho -e “load data infile ‘fact_sales_history_statistics.txt’ replace into table fact_sales_history_statistics fields terminated by ‘;’ ignore 1 lines”







Setting Up Replication Master Master

I need a Master Master replication for an application where the users are several thousand miles away from each other, looking around there seems to be a few references.  I have selected the following as a starter.

How to

I will take this as the basis as there are many positive comments and then as usual document the little bits that creep in 🙂