MySql commands#

Windows Installation (15.10.2017)#

• Download MSI Installer
• Install Developer Version (All), will go to C:\, no change possible !
• Show data dir

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

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 | 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