MySQL « Marcel Beck

watch -n 1 --differences "mysql -u username -pPassword -e 'show processlist'"

1	watch -n 1 --differences "mysql -u username -pPassword -e 'show processlist'"

GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'password' WITH GRANT OPTION;
FLUSH PRIVILEGES;

1 2	GRANT ALL PRIVILEGES ON . TO 'root'@'%' IDENTIFIED BY 'password' WITH GRANT OPTION; FLUSH PRIVILEGES;

Move a complete MySQL Database to a new Server

You can even rename the database, just use another database name on the remote server.

Dump the database direct to the remote mysql server

mysqldump --extended-insert --force --log-error=mysql_dump_error.log -u <username> -p'<password>' <database> | mysql -h <server host/ip> -u <username> -p'<password>' <database>

1	mysqldump --extended-insert --force --log-error=mysql_dump_error.log -u <username> -p'<password>' <database> \| mysql -h <server host/ip> -u <username> -p'<password>' <database>

Use ssh if you do not have direct access to the remote mysql server. (Secure)

mysqldump --extended-insert --force --log-error=mysql_dump_error.log -u <username> -p'<password>' <database> | ssh -C <ssh_user>@<server host/ip> "mysql -u <username> -p'<password>' <database>"

1	mysqldump --extended-insert --force --log-error=mysql_dump_error.log -u <username> -p'<password>' <database> \| ssh -C <ssh_user>@<server host/ip> "mysql -u <username> -p'<password>' <database>"

Move a complete MySQL Server to a new Server

Dump all databases direct to the remote mysql server

mysqldump --extended-insert --force --log-error=mysql_dump_error.log -u <username> -p'<password>' --all-databases | mysql -h <server host/ip> -u <username> -p'<password>'

1	mysqldump --extended-insert --force --log-error=mysql_dump_error.log -u <username> -p'<password>' --all-databases \| mysql -h <server host/ip> -u <username> -p'<password>'

Use ssh if you do not have direct access to the remote mysql server. (Secure)

mysqldump --extended-insert --force --log-error=mysql_dump_error.log -u <username> -p'<password>' --all-databases | ssh -C <ssh_user>@<server host/ip> "mysql -u <username> -p'<password>'"

1	mysqldump --extended-insert --force --log-error=mysql_dump_error.log -u <username> -p'<password>' --all-databases \| ssh -C <ssh_user>@<server host/ip> "mysql -u <username> -p'<password>'"

So you wanted to change the value of “innodb_log_file_size” on your mysql server.

As it happens, you can’t just change it the “my.cnf” file, restart and make it work. If you do, Mysql will refuse to start and throw this error instead. You can the details of the startup error in your mysql error log file.

To safely change this setting follow these steps

Shutdown your mysql server, make sure to check the error log that the shutdown had no issues

Move the “ib_logfile0″ and “ib_logfile1″ files from your mysql data directory.

Change “innodb_log_file_size” to your desired setting.

Restart mysql. Check the error log to make sure everything started successfully, mysql may complain that the log files don’t exist, it will make new ones and start.
Make sure if you are changing things on a production server, you take due care of the mysql downtime due to the changes and restart, and have backups to recover quickly in case something goes wrong.

InnoDB: If you are using big BLOB or TEXT rows, you must set the
InnoDB: combined size of log files at least 10 times bigger than the
InnoDB: largest such row.

# Set buffer pool size to 50-80% of your computer's memory
innodb_buffer_pool_size=256M
innodb_additional_mem_pool_size=20M
#
# Set the log file size to about 25% of the buffer pool size
innodb_log_file_size=64M
innodb_log_buffer_size=8M

Kleines Snippet um Unix Timestamps in einer Tabelle in Datetime umzuwandeln

ALTER TABLE `table1` CHANGE `time` `unix_time` int(11) NOT NULL /* rename the old column */
ALTER TABLE `table1` ADD `time` DATETIME NOT NULL /* create the datetime column */
UPDATE `table1` SET `time`=FROM_UNIXTIME(unix_time) /* convert the data */
ALTER TABLE `table1` DROP `unix_time` /* drop the old unix time column */

ALTER TABLE `table1` CHANGE `time` `unix_time` int(11) NOT NULL /* rename the old column */

ALTER TABLE `table1` ADD `time` DATETIME NOT NULL /* create the datetime column */

UPDATE `table1` SET `time`=FROM_UNIXTIME(unix_time) /* convert the data */

ALTER TABLE `table1` DROP `unix_time` /* drop the old unix time column */

Nach einem Update von MySQL bekam ich folgende Fehlermeldung, wenn ich mit MySQL Workbench die DB Schemas updaten wollte:

Cannot load from mysql.proc. The table is probably corrupted

1	Cannot load from mysql.proc. The table is probably corrupted

Dafür gibt es eine einfache Lösung:

mysql_upgrade

1	mysql_upgrade

Auf dem Server ausführen, unter Windows findet man diese Datei unter

&lt;MySQL&gt;/bin/mysql_upgrade.exe

1	<MySQL>/bin/mysql_upgrade.exe

Wenn man diese ausgeführt hat, sollte alles wieder funktionieren.

Hier eine kleine Abfrage, um Doppelte Einträge aus einer Tabelle zu löschen:

DELETE FROM table1
USING table1, table1 as vtable
WHERE
(table1.ID &gt; vtable.ID) /* Hier wird der erste Eintrag beibehalten */
/*(table1.ID &lt; vtable.ID)*/ /* Hier wird der letzte Eintrag beibehalten */
AND (table1.field_name=vtable.field_name)

DELETE FROM table1

USING table1, table1 as vtable

WHERE

(table1.ID > vtable.ID) /* Hier wird der erste Eintrag beibehalten */

/*(table1.ID < vtable.ID)*/ /* Hier wird der letzte Eintrag beibehalten */

AND (table1.field_name=vtable.field_name)

table1: Tabellenname

field_name: Feld welches zum vergleichen genutzt werden soll

Die WHERE Clause kann man beliebig erweitern.

Wenn man diese Fehlermeldung von einem MySQL Server zurück bekommt, sollte man erstmal überprüfen ob die Spalten welche für die Foreign Keys genutzt werden, die selben Datentypen sind:

Zu prüfende Werte:

typ
length
unsigned

Heute wollte mein MySQL Workbench nicht mehr per SSH auf einen MySQL Server verbinden, ich bekam nur folgende Fehlermeldungen

62842 INFO Connecting to SSH server at SERVER:22 using key &lt;public_rsa&gt;...
62842 ERROR Failed to connect to SERVER:22: WindowsError(5, 'Zugriff verweigert')
Operation failed: Error connecting SSH tunnel: [Error 5] Zugriff verweigert: 'PFAD\\temp\\tmpqpodwu.pag'

62842 INFO Connecting to SSH server at SERVER:22 using key <public_rsa>...

62842 ERROR Failed to connect to SERVER:22: WindowsError(5, 'Zugriff verweigert')

Operation failed: Error connecting SSH tunnel: [Error 5] Zugriff verweigert: 'PFAD\\temp\\tmpqpodwu.pag'

Mit dem selben SSH Key kann ich aber wunderbar mit Putty auf den Server verbinden.

Das Problem war, das ich einen RSA Key benutze.

Die Lösung, einfach den RSA Key mit PuTTYgen in einen DSA Key umwandeln.

Marcel Beck

Ich mag Züge…

Archiv der Kategorie: MySQL

MySQL – Simple Top / Processlist

MySql – Remote Root Access

MySQL – Move Database to new Server

“InnoDB: Error: log file ./ib_logfile0 is of different size” Error

MySQL – Combined size of log files

MySQL Unix Timestamp in Datetime umwandeln

MySQL – Cannot load from mysql.proc. The table is probably corrupted

MySQL – Doppelte Einträge entfernen

MySQL – ERROR 1005: Can’t create table (errno: 150) – InnoDB

MySQL Workbench – Standard TCP/IP over SSH – Key wird nicht aktzeptiert