mySQL Server 5.5 auf Debian Squeeze

Wer einen performanten mySQL Server 5.5 unter Debian Squeeze betreiben will – muss auf das normale Debian Packet verzichten und manuell installieren, da die „offizielle“ Variante aus dem Repositry leider etwas stark veraltet ist.

Ist aber grundsätzlich kein Hexenwerk – so gehts:

Sofern bestehende Datenbanken vorhanden sind – Backup erstellen! Ich gehe davon aus dass es hierzu kein HowTo braucht 😉

Alte Installation bereinigen, sofern vorhanden:

apt-get remove mysql-server mysql-common --purge && apt-get autoremove


Danach sollte die „asynchronous I/O library“, kurz AIO aus dem Repository installiert werden – damit kann die spätere Performance von InnoDB massiv verbessert werden.

apt-get install libaio-dev

Analog zu Debian, sollte mySQL auch weiterhin unter einem eigenen Benutzer laufen, dazu legen wir diesen mit entsprechender Gruppe an, sofern notwendig.

useradd mysql
groupadd mysql

Danach werden die benötigten Ordner auf dem Filesystem angelegt und mit den nötigen Rechten versehen. In diesem Beispiel wird die Struktur analog zur normalen Packetinstallation aufgebaut. Diese sollte aber wenn möglich weiter optimiert werden. Ein paar Gedanken dazu sind in einem anderen Artikel zu finden.

mkdir /var/log/mysql && chown -R mysql:mysql /var/log/mysql
mkdir -p /var/run/mysqld && chown -R mysql:root /var/run/mysqld && chmod -R 755 /var/run/mysqld
mkdir -p /var/lib/mysql && chown -R mysql:mysql /var/lib/mysql && chmod -R 700 /var/lib/mysql

Als nächstes brauchen wir nun das aktuellste „Linux Generic Tar Archive“ von mySQL:
http://www.mysql.com/downloads/mysql/#downloads

Wählen sie unbedingt das korrekte Packet. Sollten sie unsicher sein bezüglich 32 oder 64 Bit – Fragen sie doch mal ihr System dazu 😉

getconf LONG_BIT

In meinem Fall wurde die Version 5.5.15 (64 Bit) von Switch.ch heruntergeladen und entpackt:

cd /usr/local/src/ && mkdir mysql5.5 && cd mysql5.5
wget http://dev.mysql.com/get/Downloads/MySQL-5.5/mysql-5.5.15-linux2.6-x86_64.tar.gz/from/http://mirror.switch.ch/ftp/mirror/mysql/ -O mysql-5.5.15-linux2.6-x86_64.tar.gz
tar xzvf mysql-5.5.15-linux2.6-x86_64.tar.gz

In meinem Setup werden die Dateien anschliessen nach /usr/share/mysql verschoben und an den User mysql zugewiesen:

mv mysql-5.5.15-linux2.6-x86_64 /usr/local/mysql
chown mysql:mysql /usr/local/mysql/ -R

Als nächstes muss das mySQL-Bin Verzeichniss noch in den Path aufgenommen werden:

export PATH=/usr/local/mysql/bin:$PATH

Wenn alles klappt – sollte mySQL nun startklar sein. Ein kleiner Test schaded aber sicherlich nicht:

mysql -V && mysqld -V

Sollte folgendes ans Licht bringen:

mysql  Ver 14.14 Distrib 5.5.15, for linux2.6 (x86_64) using readline 5.1
mysqld  Ver 5.5.15 for linux2.6 on x86_64 (MySQL Community Server (GPL))

Wenn alles geklappt hat, sollte noch die /etc/profile mit dem neuen Pfad ergänz werden damit diese Änderung permant wird. Auszug:

...
if [ "`id -u`" -eq 0 ]; then
  PATH="/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin:/usr/local/mysql/bin"
else
  PATH="/usr/local/bin:/usr/bin:/bin:/usr/local/games:/usr/games:/usr/local/mysql/bin"
fi
export PATH
...

Nun brauchen wir noch die Konfiguration als /etc/mysql/my.cnf. Hierzu gibt es gute Templates welche übernommen werden können. In meinem Beispiel wurde die my-innodb-heavy-4G.cnf gewählt – es gäbe aber noch diverse Alternativen – am identischen Ort.

cp /usr/local/mysql/support-files/my-innodb-heavy-4G.cnf /etc/my.cnf

Folgende Anpassungen sind hier notwendig (anpassen/ergänzen):

[client]
socket          = /var/run/mysqld/mysqld.sock

[mysqld]
socket          = /var/run/mysqld/mysqld.sock
basedir         = /usr/local/mysql
datadir         = /var/lib/mysql/data
tmpdir          = /tmp
log_error       = /var/log/mysql/error.log

init.d Script erstellen:

cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysql && chmod +x /etc/init.d/mysql

Hier sind nun noch 2 weitere Anpassungen notwendig:

basedir=/usr/local/mysql
datadir=/var/lib/mysql/data

Basisdatenbanken anlegen:

/usr/local/mysql/scripts/mysql_install_db --user=mysql --basedir=/usr/local/mysql

Fertig – ein erster Startversuch:

/etc/init.d/mysql start

Sicherheits-Konfiguration setzten, dafür braucht es allerdings noch einen Symlink für den Socken:

ln -s /var/run/mysqld/mysqld.sock /tmp/mysql.sock
/usr/local/mysql/bin/mysql_secure_installation

Wird AIO auch wirklich angewendet? Ein Blick ins Log hilft weiter, folgender Eintrag muss erscheinen:

****** **:**:** InnoDB: Using Linux native AIO

Wenn alls OK ist – Server automatisch mit Bootprozess starten:

update-rc.d mysql defaults

Schlagworte: , , ,

9 Kommentare zu „mySQL Server 5.5 auf Debian Squeeze“

  1. meveo sagt:

    Hallo habe lauter fehler sowie beim starten von mysql
    alpha546:/usr/local/src# /etc/init.d/mysql start
    Starting MySQL
    ….The server quit without updating PID file (/usr/local/mysql/data/alpha546.server4you.de.pid). … failed!
    als auch bei secure_installation

    alpha546:/usr/local/src# /usr/local/mysql/bin/mysql_secure_installation

    NOTE: RUNNING ALL PARTS OF THIS SCRIPT IS RECOMMENDED FOR ALL MySQL
    SERVERS IN PRODUCTION USE! PLEASE READ EACH STEP CAREFULLY!

    In order to log into MySQL to secure it, we’ll need the current
    password for the root user. If you’ve just installed MySQL, and
    you haven’t set the root password yet, the password will be blank,
    so you should just press enter here.

    Enter current password for root (enter for none):
    ERROR 2002 (HY000): Can’t connect to local MySQL server through socket ‚/tmp/mysql.sock‘ (2)
    Enter current password for root (enter for none):

    hier meine log110915 01:32:55 mysqld_safe Starting mysqld daemon with databases from /usr/local/mysql/data
    110915 1:32:55 [Note] Plugin ‚FEDERATED‘ is disabled.
    /usr/local/mysql/bin/mysqld: Table ‚mysql.plugin‘ doesn’t exist
    110915 1:32:55 [ERROR] Can’t open the mysql.plugin table. Please run mysql_upgrade to create it.
    110915 1:32:55 InnoDB: The InnoDB memory heap is disabled
    110915 1:32:55 InnoDB: Mutexes and rw_locks use GCC atomic builtins
    110915 1:32:55 InnoDB: Compressed tables use zlib 1.2.3
    110915 1:32:55 InnoDB: Using Linux native AIO
    110915 1:32:55 InnoDB: Initializing buffer pool, size = 2.0G
    110915 1:32:56 InnoDB: Completed initialization of buffer pool
    InnoDB: The first specified data file ./ibdata1 did not exist:
    InnoDB: a new database to be created!
    110915 1:32:56 InnoDB: Setting file ./ibdata1 size to 10 MB
    InnoDB: Database physically writes the file full: wait…
    110915 1:32:56 InnoDB: Log file ./ib_logfile0 did not exist: new to be created
    InnoDB: Setting log file ./ib_logfile0 size to 256 MB
    InnoDB: Database physically writes the file full: wait…
    InnoDB: Progress in MB: 100 200
    110915 1:33:01 InnoDB: Log file ./ib_logfile1 did not exist: new to be created
    InnoDB: Setting log file ./ib_logfile1 size to 256 MB
    InnoDB: Database physically writes the file full: wait…
    InnoDB: Progress in MB: 100 200
    110915 1:33:06 InnoDB: Log file ./ib_logfile2 did not exist: new to be created
    InnoDB: Setting log file ./ib_logfile2 size to 256 MB
    InnoDB: Database physically writes the file full: wait…
    InnoDB: Progress in MB: 100 200
    InnoDB: Doublewrite buffer not found: creating new
    InnoDB: Doublewrite buffer created
    InnoDB: 127 rollback segment(s) active.
    InnoDB: Creating foreign key constraint system tables
    InnoDB: Foreign key constraint system tables created
    110915 1:33:11 InnoDB: Waiting for the background threads to start
    110915 1:33:12 InnoDB: 1.1.8 started; log sequence number 0
    110915 1:33:12 [ERROR] Fatal error: Can’t open and lock privilege tables: Table ‚mysql.host‘ doesn’t exist
    110915 01:33:12 mysqld_safe mysqld from pid file /usr/local/mysql/data/alpha546.server4you.de.pid ended
    110915 01:54:11 mysqld_safe Starting mysqld daemon with databases from /usr/local/mysql/data
    110915 1:54:11 [Note] Plugin ‚FEDERATED‘ is disabled.
    /usr/local/mysql/bin/mysqld: Table ‚mysql.plugin‘ doesn’t exist
    110915 1:54:11 [ERROR] Can’t open the mysql.plugin table. Please run mysql_upgrade to create it.
    110915 1:54:11 InnoDB: The InnoDB memory heap is disabled
    110915 1:54:11 InnoDB: Mutexes and rw_locks use GCC atomic builtins
    110915 1:54:11 InnoDB: Compressed tables use zlib 1.2.3
    110915 1:54:11 InnoDB: Using Linux native AIO
    110915 1:54:11 InnoDB: Initializing buffer pool, size = 2.0G
    110915 1:54:11 InnoDB: Completed initialization of buffer pool
    110915 1:54:11 InnoDB: highest supported file format is Barracuda.
    InnoDB: Log scan progressed past the checkpoint lsn 48941
    110915 1:54:11 InnoDB: Database was not shut down normally!
    InnoDB: Starting crash recovery.
    InnoDB: Reading tablespace information from the .ibd files…
    InnoDB: Restoring possible half-written data pages from the doublewrite
    InnoDB: buffer…
    InnoDB: Doing recovery: scanned up to log sequence number 1595675
    110915 1:54:12 InnoDB: Starting an apply batch of log records to the database…
    InnoDB: Progress in percents: 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99
    InnoDB: Apply batch completed
    110915 1:54:13 InnoDB: Waiting for the background threads to start
    110915 1:54:14 InnoDB: 1.1.8 started; log sequence number 1595675
    110915 1:54:14 [Note] Recovering after a crash using mysql-bin
    110915 1:54:14 [Note] Starting crash recovery…
    110915 1:54:14 [Note] Crash recovery finished.
    110915 1:54:14 [ERROR] Fatal error: Can’t open and lock privilege tables: Table ‚mysql.host‘ doesn’t exist
    110915 01:54:14 mysqld_safe mysqld from pid file /usr/local/mysql/data/alpha546.server4you.de.pid ended
    110915 02:00:46 mysqld_safe Starting mysqld daemon with databases from /usr/local/mysql/data
    110915 2:00:46 [Note] Plugin ‚FEDERATED‘ is disabled.
    /usr/local/mysql/bin/mysqld: Table ‚mysql.plugin‘ doesn’t exist
    110915 2:00:46 [ERROR] Can’t open the mysql.plugin table. Please run mysql_upgrade to create it.
    110915 2:00:46 InnoDB: The InnoDB memory heap is disabled
    110915 2:00:46 InnoDB: Mutexes and rw_locks use GCC atomic builtins
    110915 2:00:46 InnoDB: Compressed tables use zlib 1.2.3
    110915 2:00:46 InnoDB: Using Linux native AIO
    110915 2:00:46 InnoDB: Initializing buffer pool, size = 2.0G
    110915 2:00:47 InnoDB: Completed initialization of buffer pool
    110915 2:00:47 InnoDB: highest supported file format is Barracuda.
    InnoDB: Log scan progressed past the checkpoint lsn 48941
    110915 2:00:47 InnoDB: Database was not shut down normally!
    InnoDB: Starting crash recovery.
    InnoDB: Reading tablespace information from the .ibd files…
    InnoDB: Restoring possible half-written data pages from the doublewrite
    InnoDB: buffer…
    InnoDB: Doing recovery: scanned up to log sequence number 1595675
    110915 2:00:47 InnoDB: Starting an apply batch of log records to the database…
    InnoDB: Progress in percents: 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99
    InnoDB: Apply batch completed
    110915 2:00:48 InnoDB: Waiting for the background threads to start
    110915 2:00:49 InnoDB: 1.1.8 started; log sequence number 1595675
    110915 2:00:49 [Note] Recovering after a crash using mysql-bin
    110915 2:00:49 [Note] Starting crash recovery…
    110915 2:00:49 [Note] Crash recovery finished.
    110915 2:00:49 [ERROR] Fatal error: Can’t open and lock privilege tables: Table ‚mysql.host‘ doesn’t exist
    110915 02:00:49 mysqld_safe mysqld from pid file /usr/local/mysql/data/alpha546.server4you.de.pid ended
    110915 02:04:03 mysqld_safe Starting mysqld daemon with databases from /usr/local/mysql/data
    110915 2:04:03 [Note] Plugin ‚FEDERATED‘ is disabled.
    /usr/local/mysql/bin/mysqld: Table ‚mysql.plugin‘ doesn’t exist
    110915 2:04:03 [ERROR] Can’t open the mysql.plugin table. Please run mysql_upgrade to create it.
    110915 2:04:03 InnoDB: The InnoDB memory heap is disabled
    110915 2:04:03 InnoDB: Mutexes and rw_locks use GCC atomic builtins
    110915 2:04:03 InnoDB: Compressed tables use zlib 1.2.3
    110915 2:04:03 InnoDB: Using Linux native AIO
    110915 2:04:03 InnoDB: Initializing buffer pool, size = 2.0G
    110915 2:04:03 InnoDB: Completed initialization of buffer pool
    110915 2:04:03 InnoDB: highest supported file format is Barracuda.
    InnoDB: Log scan progressed past the checkpoint lsn 48941
    110915 2:04:03 InnoDB: Database was not shut down normally!
    InnoDB: Starting crash recovery.
    InnoDB: Reading tablespace information from the .ibd files…
    InnoDB: Restoring possible half-written data pages from the doublewrite
    InnoDB: buffer…
    InnoDB: Doing recovery: scanned up to log sequence number 1595675
    110915 2:04:03 InnoDB: Starting an apply batch of log records to the database…
    InnoDB: Progress in percents: 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99
    InnoDB: Apply batch completed
    110915 2:04:04 InnoDB: Waiting for the background threads to start
    110915 2:04:05 InnoDB: 1.1.8 started; log sequence number 1595675
    110915 2:04:05 [Note] Recovering after a crash using mysql-bin
    110915 2:04:05 [Note] Starting crash recovery…
    110915 2:04:05 [Note] Crash recovery finished.
    110915 2:04:05 [ERROR] Fatal error: Can’t open and lock privilege tables: Table ‚mysql.host‘ doesn’t exist
    110915 02:04:05 mysqld_safe mysqld from pid file /usr/local/mysql/data/alpha546.server4you.de.pid ended
    110915 02:10:50 mysqld_safe Starting mysqld daemon with databases from /usr/local/mysql/data
    110915 2:10:50 [Note] Plugin ‚FEDERATED‘ is disabled.
    /usr/local/mysql/bin/mysqld: Table ‚mysql.plugin‘ doesn’t exist
    110915 2:10:50 [ERROR] Can’t open the mysql.plugin table. Please run mysql_upgrade to create it.
    110915 2:10:50 InnoDB: The InnoDB memory heap is disabled
    110915 2:10:50 InnoDB: Mutexes and rw_locks use GCC atomic builtins
    110915 2:10:50 InnoDB: Compressed tables use zlib 1.2.3
    110915 2:10:50 InnoDB: Using Linux native AIO
    110915 2:10:50 InnoDB: Initializing buffer pool, size = 2.0G
    110915 2:10:51 InnoDB: Completed initialization of buffer pool
    110915 2:10:51 InnoDB: highest supported file format is Barracuda.
    InnoDB: Log scan progressed past the checkpoint lsn 48941
    110915 2:10:51 InnoDB: Database was not shut down normally!
    InnoDB: Starting crash recovery.
    InnoDB: Reading tablespace information from the .ibd files…
    InnoDB: Restoring possible half-written data pages from the doublewrite
    InnoDB: buffer…
    InnoDB: Doing recovery: scanned up to log sequence number 1595675
    110915 2:10:51 InnoDB: Starting an apply batch of log records to the database…
    InnoDB: Progress in percents: 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99
    InnoDB: Apply batch completed
    110915 2:10:52 InnoDB: Waiting for the background threads to start
    110915 2:10:53 InnoDB: 1.1.8 started; log sequence number 1595675
    110915 2:10:53 [Note] Recovering after a crash using mysql-bin
    110915 2:10:53 [Note] Starting crash recovery…
    110915 2:10:53 [Note] Crash recovery finished.
    110915 2:10:53 [ERROR] Fatal error: Can’t open and lock privilege tables: Table ‚mysql.host‘ doesn’t exist
    110915 02:10:53 mysqld_safe mysqld from pid file /usr/local/mysql/data/alpha546.server4you.de.pid ended
    110915 02:14:01 mysqld_safe Starting mysqld daemon with databases from /usr/local/mysql/data
    110915 2:14:01 [Note] Plugin ‚FEDERATED‘ is disabled.
    /usr/local/mysql/bin/mysqld: Table ‚mysql.plugin‘ doesn’t exist
    110915 2:14:01 [ERROR] Can’t open the mysql.plugin table. Please run mysql_upgrade to create it.
    110915 2:14:01 InnoDB: The InnoDB memory heap is disabled
    110915 2:14:01 InnoDB: Mutexes and rw_locks use GCC atomic builtins
    110915 2:14:01 InnoDB: Compressed tables use zlib 1.2.3
    110915 2:14:01 InnoDB: Using Linux native AIO
    110915 2:14:01 InnoDB: Initializing buffer pool, size = 2.0G
    110915 2:14:01 InnoDB: Completed initialization of buffer pool
    110915 2:14:01 InnoDB: highest supported file format is Barracuda.
    InnoDB: Log scan progressed past the checkpoint lsn 49439
    110915 2:14:01 InnoDB: Database was not shut down normally!
    InnoDB: Starting crash recovery.
    InnoDB: Reading tablespace information from the .ibd files…
    InnoDB: Restoring possible half-written data pages from the doublewrite
    InnoDB: buffer…
    InnoDB: Doing recovery: scanned up to log sequence number 1595675
    110915 2:14:01 InnoDB: Starting an apply batch of log records to the database…
    InnoDB: Progress in percents: 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99
    InnoDB: Apply batch completed
    110915 2:14:02 InnoDB: Waiting for the background threads to start
    110915 2:14:03 InnoDB: 1.1.8 started; log sequence number 1595675
    110915 2:14:03 [Note] Recovering after a crash using mysql-bin
    110915 2:14:03 [Note] Starting crash recovery…
    110915 2:14:03 [Note] Crash recovery finished.
    110915 2:14:03 [ERROR] Fatal error: Can’t open and lock privilege tables: Table ‚mysql.host‘ doesn’t exist
    110915 02:14:03 mysqld_safe mysqld from pid file /usr/local/mysql/data/alpha546.server4you.de.pid ended

    was mache ich falsch bitte helft mir

  2. Alonso sagt:

    Test mal die Rechte auf deinem Datenordner (z.B. /usr/local/mysql/data). Ist dort etwas drin?

    Hat das mysql_install_db fehlerfrei funktioniert?

    Damit das mysql_secure_installation funktioniert, brauchst du den Symlink – wie in der Anleitung beschrieben. Aber erstmal musst den den Server zum fliegen bringen.

  3. meveo sagt:

    die rechte sind ok, ja da sind so einige dateien und ordner.
    mysql_install_db hat ohne fehler funktioniert
    wird die pid datei selber von mysql erstellt?
    ich probier mal einen neustart dann probier ichs noch mal.
    danke für die vorschläge

  4. meveo sagt:

    ich habe es geschafft über eine gefundene ppa
    hier eine kleine howto von mir

    Step1:
    sudo joe /etc/apt/sources.list *your editor like vim,joe…*

    Step 2:
    Enter this lines in your sources.list

    For natty this:

    deb http://ppa.launchpad.net/natha… natty main
    deb-src http://ppa.launchpad.net/natha… natty main

    For oneiric this:

    deb http://ppa.launchpad.net/natha… oneiric main
    deb-src http://ppa.launchpad.net/natha… oneiric main

    Save the sources list in vim :wq

    Step 3:Add this key
    sudo apt-key adv –keyserver keyserver.ubuntu.com –recv-keys 29A4B41A

    Step 4:

    apt-get update
    apt-get install mysql-server

    Thats it

    mysql -v

  5. meveo sagt:

    ppa ist http://ppa.launchpad.net/nathan-renniewaldock

  6. RM sagt:

    Basisdatenbank anlegen ist falsch.
    Es müsste so sein: /usr/local/mysql/scripts/mysql_install_db –user=mysql –ldata=/usr/local/mysql/data –basedir=/usr/local/mysql

    ldata war falsch.

  7. Alonso sagt:

    Danke für den Input. Wurde entsprechend korrigiert. MfG

  8. Claude sagt:

    Vielen Dank fïr das Tutorial. Es muss jedoch /var/lib/mysql/data/ bei datadir sein.

    [client]
    socket = /var/run/mysqld/mysqld.sock

    [mysqld]
    socket = /var/run/mysqld/mysqld.sock
    basedir = /usr/local/mysql
    datadir = /var/lib/mysql/data/
    tmpdir = /tmp
    log_error = /var/log/mysql/error.log

  9. luni sagt:

    neustes paket:
    mysql-5.5.25a-linux2.6-x86_64.tar.gz

    selbes mirror funktioniert, dh nur dateinamen auktualisieren

Kommentieren


Social Widgets powered by AB-WebLog.com.