[{ALLOW view All}]
[{ALLOW edit Markus}]
!! MySql commands

!Windows Installation (15.10.2017)
* [Download|https://dev.mysql.com/downloads/windows/installer] MSI Installer
* Install Developer Version (All), will go to C:\, no change possible !
* Show [data dir|https://dba.stackexchange.com/questions/156017/during-installation-cant-change-directory]
{{{
C:\ProgramData\MySQL\MySQL Server 5.7\mysql -u root -p 
[enter password]
C:\ProgramData\MySQL\MySQL Server 5.7
show variables like 'datadir';
+---------------+---------------------------------------------+
| Variable_name | Value                                       |
+---------------+---------------------------------------------+
| datadir       | C:\ProgramData\MySQL\MySQL Server 5.7\Data\ |
+---------------+---------------------------------------------+
}}}
* Stop Mysql service, copy folder somewhere else (in my case F:\MySQL !)
* Change datadir in C:\ProgramData\MySQL\MySQL Server 5.7\my.ini
* Start service, verify again with mysql
* MySQl Workbench: Delete current connection and recreate it

! Create a database always with characters set !
{{{
create database <databasename> default character set 'UTF8';
}}}

{{{
mysql -u <user> -p<password> 
mysql -u <user> -p<password> -v
create database <database>;
show databases;
use <database>;
show tables;
select * from <table>;
}}}

! Start and Stop
{{{
mysqladmin -u root -p shutdown
}}}

! MySql als Windows Dienst
Wenn der Dienst verschwunden ist (bei mir nach dem Windows 8.0->8.1 Update), dann
{{{
<MySqlHomeDir>\MySQL Server 5.6\bin\mysqld --install
}}}

!!Backup MySQL DatePlanner

For Rails run this BEFORE TO CREATE table schema_info
{{{
$ rake db:migrate
}}}

!Option 1

{{{
SELECT * FROM dateitems INTO OUTFILE 'EXP_dateitems.dat';
 [FIELDS TERMINATED BY ',' ENCLOSED BY '"'LINES TERMINATED BY '\r\n';9]
}}}
=> creates a file under /var/lib/mysql/DatePlanner_development/dateitems.dat

!Option 2

{{{
mysql -h localhost -u <user> -p<password> DatePlanner_development
SELECT * FROM dateitems INTO OUTFILE '/markus/workspace/DatePlanner/EXP_dateitems.dat';
SELECT * FROM dategroups INTO OUTFILE '/markus/workspace/DatePlanner/EXP_dategroups.dat';
SELECT * FROM users INTO OUTFILE '/markus/workspace/DatePlanner/EXP_users.dat';
}}}
you need to give chmod 777 to the folder where to export, because it is done with user mysql

!Option 3 (best)

{{{
mysqldump -u <user> -p<password> DatePlanner_development dateitems dategroups users > 20090120_DatePlannerDB.sql
mysql -u <user> -p DatePlanner_development < 20090120_DatePlannerDB.sql
}}}

!Option 4 - whole database
You can also copy the whole database (including all tables) by omitting the table names:
{{{
mysqldump -u <user> -p<password> opencms > /anyfolder/20141012_opencms.sql
mysql -u <user> -p<password> opencms < /anyfolder/20141012_opencms.sql

mysqldump -u root -p --opt [database name] > [database name].sql
mysql -u root -p newdatabase < /path/to/newdatabase.sql
}}}

Import in Windows also possible with MySql Workbench, see
[here|http://www.ebel-computing.de/JSPWiki/Wiki.jsp?page=Eclipse#section-Eclipse-5.MySql]

{{{
mysqldump -u <user> -p<password> homepage links > homepage.sql
mysql -u <user> -p<password> homepage < homepage.sql
}}}

You can additionally pack/unpack
{{{
tar -zcvf archive-name.tar.gz sourceFolder
tar -zxvf archive-name.tar.gz [-C /targetFolder]
}}}


----
[CHARSET from latin1 to utf8|http://yoonkit.blogspot.com/2006/03/mysql-charset-from-latin1-to-utf8.html] | [External Access With JDBC]

!Step By Step Backup
Export
{{{
mysql -u <user> -p<password>
show databases;
use <database>;
show tables;
mysqldump -u <user> -p<password> <table1> <table2> <...> > filename.sql

Examples:
mysqldump -u root -p<pw> DatePlanner_development dateitems dategroups users > 20100930_DatePlanner_development_DB.sql
mysqldump -u root -p<pw> homepage links markers users > 20100930_homepage_DB.sql
}}}
Import
{{{
create database <databasename> default character set 'UTF8';

mysql -u root -p<password> DatePlanner_development < 20100930_DatePlanner_development_DB.sql
mysql -u root -p<password> homepage < 20100930_homepage_DB.sql
}}}