Create MySQL database and user (including MYSQLdump)

IT IS NOT FINISHED YET

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…

 

e.g.

 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 🙂