Content mit Geschäftliches MySQL .

« Zurück

MySQL: Alte Clients mit neuen Servern machen Probleme mit den Character-Sets

Um das auszubremsen nehme man z.B. 

SET NAMES 'latin1';


Dann werden die Language-Einstellungen so gesetzt, wie der alte Client sie erwartet. Zu kontrollieren mit:

show variables like 'character_set%';

« Zurück

Mysql-Dump mit nur 100 Zeilen pro Tabelle

Manchmal braucht man nicht alles aus den Tabellen. Ich weiß, der Fall ist selten, aber er kommt vor:

Hier ist eine Lösung:

Man braucht dazu diese Zeile:

mysqldump -u [username] -p [dbname] {--opt} --where="true limit 100" > dump.sql


« Zurück

Exporting grants from mysql

How to transfer a set of user's grants from one mysql server to another?


There is a great answer on serverfault.

The essential part is to define this function in bash:

  mysql -B -N $@ -e "SELECT DISTINCT CONCAT(
    'SHOW GRANTS FOR \'', user, '\'@\'', host, '\';'
    ) AS query FROM mysql.user" | \
  mysql $@ | \
  sed 's/\(GRANT .*\)/\1;/;s/^\(Grants for .*\)/## \1 ##/;/##/{x;p;x;}'

edit the skript and remove all double backslashes (\\) and then use it like this:

mygrants --host=prod-db1 --user=admin --password=secret | grep rails_admin | mysql --host=staging-db1 --user=admin --password=secret



« Zurück

Monitor restoring a big database in MySQL

To Dump a database is simple, just use something like

mysqldump -u <user> -p <databasename> > dump_file.sql

It will take its time but there is little one can do to speed it up.

When pulling this dump back into a possibly different database you have some options. Of course you can just use

mysql -u [user] -p [database] < dump_file.sql

But you will see that it takes a lot more time. And there is nothing you can do to monitor this. Hints for speed up include: Always put the dump file on a local file system, not on NFS nor Samba etc.

For monitoring you can use split to break the file into parts, cat them into the mysql utility and then monitor the file descriptors of cat:

mkdir splits

split -n 2000 dump_file.sql splits/df_

cat splits/df_* | mysql -u [user] -p [database]

In another shell you can now do "ps auxww|grep cat" to find out the process ID of cat and with

sudo ls -l /proc/[proc_id]/fd/

you see the list of open files of cat. One of them describes the file currently read and by the name of the file you can easily tell where in the process you currently are.

« Zurück

How to rescue MySQL replication

The MySQL Replication ist very delicate since it replicates nearly every command on the master server.

Sometimes it comes toi situations where the replication stops. This can be veryfied by the mysql command

show slave status \G

The "\G" in the end makes the result of the query be shown vertically instead of a horizontal table. Normally you can also find the reason for the replication stopper in the result of this query. After having repaired the problem (e.g. a missing column in a table on the client), you can restart the replication on the client with

start slave;

If you have to inspect the binlog on the master use the command

mysqlbinlog <filename> | less

Of course you must find out the name of the last binlog file.

Sometimes it is helpful to skip one or more SQL statements in replication. To accomplish this stop the replication if it isn't stopped on error yet, the execute:



« Zurück

MySQL: Neuen Benutzer und neue DB anlegen

mysql -u root -p -h localhost

> create database times;

>  grant all privileges on times.* to 'times_admin'@'localhost' identified by '******';

>  grant all privileges on times.* to 'times_admin'@'192.168.46.%' identified by '******';