MySQL andmebaasi kasutamine operatsioonisüsteemiga Debian
Sissejuhatus
Debian Squeeze v. 6.0 sisaldab MySQL andmebaasi v 5.1 tarkvara.
Tarkvara paigaldamine
Tarkvara paigaldamiseks tuleb öelda, kusjuures küsitakse MySQL serveri juurkasutaja parooli
# apt-get install mysql-server
Andmebaasi käivitab skript
# /etc/init.d/mysql start|stop|reload|...
Andmefailid asuvad kataloogis /var/lib/mysql, iga nn create database andmebaas omaette alamkataloogis
# ls -l /var/lib/mysql total 20528 -rw-r--r-- 1 mysql mysql 0 Mar 14 09:32 debian-5.1.flag -rw-rw---- 1 mysql mysql 5242880 Apr 10 17:56 ib_logfile0 -rw-rw---- 1 mysql mysql 5242880 Mar 14 09:32 ib_logfile1 -rw-rw---- 1 mysql mysql 10485760 Apr 10 17:56 ibdata1 drwx------ 2 mysql mysql 4096 Apr 10 17:10 mysql drwx------ 2 mysql mysql 4096 Apr 10 17:10 tartu drwx------ 2 mysql mysql 4096 Apr 10 17:10 elva -rw------- 1 mysql mysql 6 Mar 14 09:33 mysql_upgrade_info
Tarkvara seadistamine
MySQL andmebaasi tööd kontrollivad seadistusfailid
- /etc/mysql/my.cnf - see juhib nii serverit kui klienti (kui serveris vahel kasutatakse klienti)
- /etc/mysql/debian.cnf - seda seadistusfaili kasutab Debiani paketihaldus MySQL baasi haldustegevuste juures
Debian operatsioonisüsteemi puhul on lisaks võetud kasutusele baasi sisemine kasutaja debian-sys-maint, mis on tingimata vajalik ning mille parool peab olema sama admebaasis ning debian.cnf seadistusfailis.
Seadistusfailis saab kasutada
- [client]
- [server]
- [mysqldump] - dumpimisega seotud tegevused, nt 'nodata'
- TODO
mysql ja mysqldump programmidele saab --defaults-file=/etc/mysql/failinimi.cnf abil näidata, millist seadistusfaili parasjagu kasutada.
Andmebaasi haldamine
Peale andmebaasi paigaldamist on moodustatud automaatselt kaks andmebaasi
- mysql - sisaldab muu hulgas tabeleid db ja user kasutajaligipääsude kohta
- information_schema
Rakenduste poolt sobiva kasutamiseks UTF-8 toega andmebaasi moodustamiseks sobib öelda
$ mysql -u root -pparool -h hostname Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 31 Server version: 5.1.49-3 (Debian) Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql> create database baasinimi character set utf8 collate utf8_general_ci;
Kasutaja lisamiseks, kellel on ligipääs moodustatud baasile sobib öelda
mysql> grant all privileges on baasinimi.* to 'kasutajanimi'@'%' identified by 'parool'; mysql> grant all privileges on baasinimi.* to 'kasutajanimi'@'localhost' identified by 'parool';
Tulemuse vaatamiseks tuleb öelda
mysql> show grants for 'baasinimi'@'%'; +---------------------------------------------------------------------------------------------------------+ | Grants for baasinimi@% | +---------------------------------------------------------------------------------------------------------+ | GRANT USAGE ON *.* TO 'kasutajanimi'@'%' IDENTIFIED BY PASSWORD '*3510076A4409150F90B46A3194793344089E3F55' | | GRANT ALL PRIVILEGES ON `baasinimi`.* TO 'kasutajanimi'@'%' | +---------------------------------------------------------------------------------------------------------+ 2 rows in set (0.00 sec)
Ligipääsuõiguste eemaldamiseks sobib öelda
mysql> revoke all on baasinimi.* from 'kasutajanimi'@'localhost';
Kasutaja eemaldamiseks sobib öelda
mysql> drop user 'kasutajanimi'@'localhost';
Baasi kustutamiseks tuleb öelda
myslq> drop database baasinimi;
Kasutaja kustutamiseks tuleb öelda
mysql> drop user kasutajanimi;
MySQL andmebaasi kasutamisel ei ole vajalik erinevalt paljudest teistest baasidest, nt PostgreSQL teha regulaarselt vacuumi.
Tabeli omaduste küsimiseks, sh storage engine
mysql> SHOW TABLE STATUS;
või
$ mysqlshow --status -u root -pparool baasinimi
# mysqlshow -S /var/run/mysqld/mysqld3.sock -u root -pparool mysql user Host Database: mysql Table: user Wildcard: Host +-------+----------+-----------+------+-----+---------+-------+---------------------------------+---------+ | Field | Type | Collation | Null | Key | Default | Extra | Privileges | Comment | +-------+----------+-----------+------+-----+---------+-------+---------------------------------+---------+ | Host | char(60) | utf8_bin | NO | PRI | | | select,insert,update,references | | +-------+----------+-----------+------+-----+---------+-------+---------------------------------+---------+
Parasjagu kehtestatud andmebaasi seadistusi saab esitada öeldes nt
# mysql -S /var/run/mysqld/mysqld.sock -u root mysql -e "show variables like '%max_binlog_siz%';" +-----------------+-----------+ | Variable_name | Value | +-----------------+-----------+ | max_binlog_size | 104857600 | +-----------------+-----------+
kõigi parameetrite esitamiseks
# mysql -S /var/run/mysqld/mysqld.sock -u root mysql -e 'show variables' | less
Baasi töö lõpetamiseks sobib öelda ka
# mysqladmin -S /var/run/mysqld/mysqld3.sock -u root -parool shutdown
Olemasoleva baasi collation ja character set küsimiseks
mysql> show variables like "%collation_database%"; +--------------------+-------------------+ | Variable_name | Value | +--------------------+-------------------+ | collation_database | latin1_swedish_ci | +--------------------+-------------------+ 1 row in set (0.00 sec)
mysql> show variables like "%character_set_database%"; +------------------------+--------+ | Variable_name | Value | +------------------------+--------+ | character_set_database | latin1 | +------------------------+--------+ 1 row in set (0.00 sec)
Andmete SQL kihis kopeerimine
Andmete SQL kihis kopeerimine võib olla vajalik
- varundamisel
- arhiveerimisel
- andmete migreerimisel erinevate MySQL serverite vahel
Koopia tegemiseks võib olla sobiv öelda
$ mysqldump --opt -u kasutajanimi -pparool -h localhost baasinimi > baasinimi-20090813.sql
ja koopia kasutamiseks, st baasi sisse lugemisest, esmalt tuleb moodustada tühi andmebaas
$ mysql -u kasutajanimi -h localhost -pparool baasinimi < baasinimi-20090813.sql
Kogu MySQL baasi sisu dumpimiseks
$ mysqldump --defaults-file=/etc/mysql/debian.cnf --all-databases > all_databases.sql
Automaatne andmebaasi SQL kihis varundamine
Nt selline skript sobib kävitamiseks kord ööpäevas crontabist ja moodustab kataloogi /var/backups/mysql/pg_dumps alla kuupäeva nimelistesse alamkataloogidesse iga andmebaasi kohta ühe sql dump faili
# mkdir /srv/backup/mysql/pg_dumps # chmod 0700 /srv/backup/mysql
ning
#!/bin/bash HOSTNAME="localhost" MYSQL_PASSWD="parool" nyyd=`date +%Y%m%d` AD="srv/backup/mysql/pg_dumps/$nyyd" PATH="/bin:/usr/bin" mkdir -p /$AD mysql_baasid=`mysql -u root -p$MYSQL_PASSWD -h $HOSTNAME -e 'show databases' | sed -e '1d'` for i in $mysql_baasid do mysqldump --opt -u root -p$MYSQL_PASSWD -h $HOSTNAME $i | gzip -c > /$AD/$HOSTNAME-$nyyd-$i-mysql.sql.gz; echo "$? $i" >> /$AD/$nyyd-dbec.txt done
Tundub, et mõnus alternatiiv on kasutada sellist skripti, ei ole tarvis skripti kirjutada baasi ligipääsuks parooli
# mkdir /var/backups/mysql # chmod 0700 /var/backups/mysql
ning
#!/bin/sh
renice 20 $$ > /dev/null
mysql_baasid=`mysql --defaults-file=/etc/mysql/debian.cnf -e 'show databases' | sed -e '1d'`
for i in $mysql_baasid
do
if test "$i" != "information_schema" && test "$i" != "performance_schema"; then
mysqldump --defaults-file=/etc/mysql/debian.cnf --opt $i | gzip -c > /var/backups/mysql/mysql_$i.sql.gz;
echo "$? $i" >> /var/backups/mysql/dbec.txt
fi
done
Nii varundamise puhul tekib kataloog sql skripte ja andmete sisselugemiseks sobib neid nö käivitada
# mysql --defaults-file=/etc/mysql/debian.cfg baasinimi < skriptinimi.sql
Kui src ja dst baasid on täpselt sama versioon ning dst baas on tühi, siis on korrektne laadida sisse ka mysql nimeline andmebaas, tulemusena tekivad kasutajad, ligipääsu privileegid jms
# mysql --defaults-file=/etc/mysql/debian.cfg mysql < mysql.sql
Kuna mysql tabelis on kirjas ka debian-sys-maint kasutaja ligipääs, siis tuleb kohendada uue arvuti /etc/mysql/debian.cfg faili sisu.
Andmebaasi root kasutaja parooli resettimine
- Seisata andembaasi protsess
# /etc/init.d/mysql stop
- Käivitada --skip-grant-tables
# mysqld --skip-grant-tables
- Muuta parool
$ mysql -u root
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.1.49-3 (Debian)
..
mysql> UPDATE mysql.user SET Password=PASSWORD('parool') where user='root';
mysql> flush privileges;
mysql> Ctrl-D
- Seisata MySQL protsess kill abil
# kill PID
- Käivitada tavalisel viisil
# /etc/init.d/mysql start
Andmebaasi uuendamine 5.0 -> 5.1
Selleks, et ühes Debian v. 5 Lenny arvutis töötanud MySQL v. 5.0 andmed võtta kasutusele teises Debian v. 6 Squeeze arvutis MySQL v. 5.1 arvutis tuleb
- mõlemas arvutis on MySQL tarkvara vastavad versioonid paigaldatud
- lõpetada mõlema andmebaasi protsessid
- kopeerida vanast uude /etc/mysql/debian.cnf faili debian-sys-maint kasutaja parool
- kopeerida vanast uude /var/lib/mysql kataloogi sisu, failide omanikud peavad olema sobivad
- käivida tavapäraselt MySQL serveri protsess, seejuures uus süsteem sooritab ise automaatselt nn mysql_upgrade'i
# /etc/init.d/mysql start
- lisada nn mysql.help_* tabelid öeldes
$ mysql -u root -h localhost -pParool mysql < /usr/share/mysql/fill_help_tables.sql
Tulemusena saab andmebaasi vana sisu kasutada edasi uue v. 5.1 mootoriga, kusjuures migreeritud on nii kasutajate andmed kui andmebaaside sisu st tabelid, indeksid jms.
Andmebaasi uuendamine 5.1 -> 5.5
Selleks, et ühes Debian v. 6 Squeeze arvutis töötanud MySQL v. 5.1 andmed võtta kasutusele teises Debian v. 8 Jessie arvutis MySQL v. 5.5 arvutis tuleb
- mõlemas arvutis on MySQL tarkvara vastavad versioonid paigaldatud
- lõpetada mõlema andmebaasi protsessid
- kopeerida vanast uude /etc/mysql/debian.cnf faili debian-sys-maint kasutaja parool
- kopeerida vanast uude /var/lib/mysql kataloogi sisu, failide omanikud peavad olema sobivad (mysql:mysql)
- käivida tavapäraselt MySQL serveri protsess, seejuures uus süsteem sooritab ise automaatselt nn mysql_upgrade'i
Märkused
- InnoDB kasutamisel pöörata sellele täiendavat tähelepanu
- https://dev.mysql.com/doc/refman/5.5/en/upgrading.html
Andmebaasi uuendamine Debian v. 6 MySQL v. 5.5.47 -> Debian v. 8 MySQL v. 5.5.59
Selleks, et ühes Debian v. 6 Squeeze arvutis töötanud MySQL v. 5.5.47 andmed võtta kasutusele teises Debian v. 8 Jessie arvutis MySQL v. 5.5.59 arvutis tuleb
- mõlemas arvutis on MySQL tarkvara vastavad versioonid paigaldatud
- lõpetada mõlema andmebaasi protsessid
- kopeerida vanast uude /etc/mysql/debian.cnf faili debian-sys-maint kasutaja parool
- kopeerida vanast uude /var/lib/mysql kataloogi sisu, failide omanikud peavad olema sobivad (mysql:mysql)
- käivida tavapäraselt MySQL serveri protsess, seejuures uus süsteem sooritab ise automaatselt nn mysql_upgrade'i
esimesel käivitamisel öeldakse tõenäoliselt logisse /var/log/mysql.err midagi sellist
180123 21:55:06 [Warning] Using unique option prefix myisam-recover instead of myisam-recover-options is deprecated and will be removed in a future release. Please use the full name instead. 180123 21:55:06 [Note] Plugin 'FEDERATED' is disabled. 180123 21:55:06 InnoDB: The InnoDB memory heap is disabled 180123 21:55:06 InnoDB: Mutexes and rw_locks use GCC atomic builtins 180123 21:55:06 InnoDB: Compressed tables use zlib 1.2.8 180123 21:55:06 InnoDB: Using Linux native AIO 180123 21:55:06 InnoDB: Initializing buffer pool, size = 128.0M 180123 21:55:06 InnoDB: Completed initialization of buffer pool 180123 21:55:06 InnoDB: highest supported file format is Barracuda. InnoDB: 127 rollback segment(s) active. 180123 21:55:06 InnoDB: Waiting for the background threads to start 180123 21:55:07 InnoDB: 5.5.59 started; log sequence number 2741210993 180123 21:55:07 [Note] Server hostname (bind-address): '127.0.0.1'; port: 3306 180123 21:55:07 [Note] - '127.0.0.1' resolves to '127.0.0.1'; 180123 21:55:07 [Note] Server socket created on IP: '127.0.0.1'. 180123 21:55:07 [ERROR] Missing system table mysql.proxies_priv; please run mysql_upgrade to create it 180123 21:55:07 [ERROR] Native table 'performance_schema'.'events_waits_current' has the wrong structure 180123 21:55:07 [ERROR] Native table 'performance_schema'.'events_waits_history' has the wrong structure 180123 21:55:07 [ERROR] Native table 'performance_schema'.'events_waits_history_long' has the wrong structure 180123 21:55:07 [ERROR] Native table 'performance_schema'.'setup_consumers' has the wrong structure 180123 21:55:07 [ERROR] Native table 'performance_schema'.'setup_instruments' has the wrong structure 180123 21:55:07 [ERROR] Native table 'performance_schema'.'setup_timers' has the wrong structure 180123 21:55:07 [ERROR] Native table 'performance_schema'.'performance_timers' has the wrong structure 180123 21:55:07 [ERROR] Native table 'performance_schema'.'threads' has the wrong structure 180123 21:55:07 [ERROR] Native table 'performance_schema'.'events_waits_summary_by_thread_by_event_name' has the wrong structure 180123 21:55:07 [ERROR] Native table 'performance_schema'.'events_waits_summary_by_instance' has the wrong structure 180123 21:55:07 [ERROR] Native table 'performance_schema'.'events_waits_summary_global_by_event_name' has the wrong structure 180123 21:55:07 [ERROR] Native table 'performance_schema'.'file_summary_by_event_name' has the wrong structure 180123 21:55:07 [ERROR] Native table 'performance_schema'.'file_summary_by_instance' has the wrong structure 180123 21:55:07 [ERROR] Native table 'performance_schema'.'mutex_instances' has the wrong structure 180123 21:55:07 [ERROR] Native table 'performance_schema'.'rwlock_instances' has the wrong structure 180123 21:55:07 [ERROR] Native table 'performance_schema'.'cond_instances' has the wrong structure 180123 21:55:07 [ERROR] Native table 'performance_schema'.'file_instances' has the wrong structure 180123 21:55:07 [Note] Event Scheduler: Loaded 0 events 180123 21:55:07 [Note] /usr/sbin/mysqld: ready for connections. Version: '5.5.59-0+deb8u1-log' socket: '/var/run/mysqld/mysqld.sock' port: 3306 (Debian)
Järgmistel käivitamistel selliseid probleeme pole ('.. has the wrong structure'). Peale edukat muudatust peaks olema failis kirjas nii
# cat /var/lib/mysql/mysql_upgrade_info 5.5.59
Andmebaasi uuendamine MySQL v. 5.5 -> MariaDB v. 10.1
Seoses Debian v. 8 -> v. 9 uuendusega asendub MySQL v. 5.5 MariaDB v. 10.1 tarkvaraga. Idee poolest peaks baasi andmestik automaatselt nö in-place teisenduma uude formaati. Maksab jälgida /var/log/mysql/error.log ja /var/log/syslog. Tõenäoliselt on error.log failis
2017-11-27 2:25:19 140297390719552 [ERROR] Incorrect definition of table mysql.event: expected column 'sql_mode' at position 14 to have type set('REAL_AS_FLOAT','PIPES_AS_CONCAT','ANSI_QUOTES',...'STRICT_A
2017-11-27 2:25:19 140297390719552 [ERROR] mysqld: Event Scheduler: An error occurred when initializing system tables. Disabling the Event Scheduler.
Võib olla on abiks öelda
# mysql_upgrade -h localhost -u debian-sys-maint -pparool
Kasulikud lisamaterjalid
Andmebaasi parandamine
Probleem paistab sedasi
mysql> show table status; +----------------------------------+--------+---------+------------+-- | Name | Engine | Version | Rows | .... +----------------------------------+--------+---------+------------+--- | jos_sefexts | MyISAM | 10 | Dynamic | 0 | jos_session | NULL | NULL | NULL | NULL ...
ja nii
mysql> check table jos_session; +-------------------+-------+----------+---------------------------------------------------+ | Table | Op | Msg_type | Msg_text | +-------------------+-------+----------+---------------------------------------------------+ | vfcon.jos_session | check | warning | Table is marked as crashed and last repair failed | | vfcon.jos_session | check | error | Found 32 keys of -14 | | vfcon.jos_session | check | error | Corrupt | +-------------------+-------+----------+---------------------------------------------------+
Edasiseks tuleb andmebaasi protsessid sulgeda.
# cd /var/lib/mysql/baasinimi # myisamchk jos_session Checking MyISAM file: jos_session Data records: -14 Deleted blocks: 0 myisamchk: warning: Table is marked as crashed and last repair failed - check file-size - check record delete-chain - check key delete-chain - check index reference - check data record references index: 1 myisamchk: error: Found 32 keys of -14 - check record links myisamchk: error: Unexpected byte: 0 at link: 2649388 MyISAM-table 'jos_session' is corrupted Fix it using switch "-r" or "-o"
Parandamine paistab sedasi
# myisamchk -r jos_session - recovering (with keycache) MyISAM-table 'jos_session' Data records: -14 Data records: 32 server:/var/lib/mysql/vfcon# myisamchk jos_session Checking MyISAM file: jos_session Data records: 32 Deleted blocks: 0 - check file-size - check record delete-chain - check key delete-chain - check index reference - check data record references index: 1 - check data record references index: 2 - check data record references index: 3 ...
Mõnel juhul võib olla abi öelda töötavas baasis
sql> optimize table tabelinimi; sql> analyize table tabelinimi;
Misc
skeemi info dumpimine
# mysqldump -u zabbix -pzabbix -d zabbix
Salvestusmootorite kasutamine
MySQL andmebaas võimaldab kasutada erinevaid salvestusmootoreid (ingl. k. storage engine), milliseid konkreetsel juhul saab kasutada sõltub, milliste tugi on kompileeritud, töötava baasi käest saab seda küsida selliselt
mysql> show engines; +------------+---------+----------------------------------------------------------------+--------------+------+------------+ | Engine | Support | Comment | Transactions | XA | Savepoints | +------------+---------+----------------------------------------------------------------+--------------+------+------------+ | InnoDB | YES | Supports transactions, row-level locking, and foreign keys | YES | YES | YES | | MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO | | BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | NO | NO | NO | | CSV | YES | CSV storage engine | NO | NO | NO | | MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO | | FEDERATED | NO | Federated MySQL storage engine | NULL | NULL | NULL | | ARCHIVE | YES | Archive storage engine | NO | NO | NO | | MyISAM | DEFAULT | Default engine as of MySQL 3.23 with great performance | NO | NO | NO | +------------+---------+----------------------------------------------------------------+--------------+------+------------+ 8 rows in set (0.09 sec)
CSV
CSV (comma separated values) salvestusmootor on mõnus kui on vaja nt ajutiselt teha CSV vormingus tekstifail andmebaasi liidese kaudu kättesaadavaks või olemasolevad andmed teisendada CSV kujule; seejuures peab arvestama, et CVS ei võimalda kasutada nt indekseid. Tabeli tekitamise ja andmete lisamise järel
mysql> create table inimesed (nimi varchar(15) NOT NULL, vanus int NOT NULL, elukoht varchar(18) NOT NULL) ENGINE=CSV;
mysql> INSERT INTO inimesed VALUES ('Mart', '16', 'Narva');
mysql> INSERT INTO inimesed VALUES ('Laa', '12', 'Kuressaare');
mysql> INSERT INTO inimesed VALUES ('Priit', '17', 'Tallinn');
tekivad failisüsteemi kolm faili, kusjuures *.CSV faili sisu on nö ascii tekst
$ cat /var/lib/mysql/test/inimesed.CSV "Mart",16,"Narva" "Laa",12,"Kuressaare" "Priit",17,"Tallinn"
Tabeli engine'it saab ka muuta, nt
mysql> ALTER TABLE inimesed ENGINE = INNODB; Query OK, 6 rows affected (0.02 sec) Records: 6 Duplicates: 0 Warnings: 0
MyISAM
TODO
InnoDB
InnoDB võimaldab failisüsteemis andmeid hoida kahel erineval viisil
- kõik andmebaasi tabelid koos ühes nn shared tablespace'ile vastavas failis (nt /var/lib/mysql/ibdata1 fail)
- igale andmebaasi tabelile vastab üks tablespace fail (nt /var/lib/mysql/zabbix/history_uint.ibd)
Üldiselt võiks eelistada teist juhtumit, kuna mõnel andmete korrumpeerumise juhtumil saab sel juhul tõenäoliselt kõik muud tabelid teha kättesaadavaks ja katkised välja jätta. Igale tabelile eraldi tablespace kasutamine lülitatakse sisse seadistusparameetriga
innodb_file_per_table
Kõik seejärel tekitatud InnoDB salvestusmootoriga tabelid salvestatakse eraldi failidesse; varem tekitatud INNODB shared tablespace'iga tabelid saab teisendada öeldes
mysql> alter table history_uint ENGINE=INNODB;
Mitme MySQL serveri eksemplari kasutamine ühes operatsioonisüsteemi keskkonnas
NB! See asi vist ei ole siiski niisama lihtne ...
Debiani vaikimisi MySQL serveri paigaldust lähtepunktina kasutades, sobiks lisada seadistusfaili sektsioon
[mysqld_multi] mysqld = /usr/bin/mysqld_safe mysqladmin = /usr/bin/mysqladmin
ning [mysqld] sektsioon asendada kahe sellisega
[mysqld1] user = mysql pid-file = /var/run/mysqld/mysqld1.pid socket = /var/run/mysqld/mysqld1.sock port = 3306 basedir = /usr datadir = /var/lib/mysql1 tmpdir = /tmp lc-messages-dir = /usr/share/mysql skip-external-locking bind-address = 127.0.0.1 key_buffer = 16M max_allowed_packet = 16M thread_stack = 192K thread_cache_size = 8 myisam-recover = BACKUP query_cache_limit = 1M query_cache_size = 16M log_error = /var/log/mysql/mysql1-error.log expire_logs_days = 10 max_binlog_size = 100M [mysqld2] user = mysql pid-file = /var/run/mysqld/mysqld2.pid socket = /var/run/mysqld/mysqld2.sock port = 3307 basedir = /usr datadir = /var/lib/mysql2 tmpdir = /tmp lc-messages-dir = /usr/share/mysql skip-external-locking bind-address = 127.0.0.1 key_buffer = 16M max_allowed_packet = 16M thread_stack = 192K thread_cache_size = 8 myisam-recover = BACKUP query_cache_limit = 1M query_cache_size = 16M log_error = /var/log/mysql/mysql2-error.log expire_logs_days = 10 max_binlog_size = 100M general_log_file = /tmp/query.log general_log = 1
# mysql_install_db --user=mysql --datadir=/var/lib/mysql1 Installing MySQL system tables... OK Filling help tables... OK To start mysqld at boot time you have to copy support-files/mysql.server to the right place for your system PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER ! To do so, start the server, then issue the following commands: /usr/bin/mysqladmin -u root password 'new-password' /usr/bin/mysqladmin -u root -h post-relay.auul password 'new-password' Alternatively you can run: /usr/bin/mysql_secure_installation which will also give you the option of removing the test databases and anonymous user created by default. This is strongly recommended for production servers. See the manual for more instructions. You can start the MySQL daemon with: cd /usr ; /usr/bin/mysqld_safe & You can test the MySQL daemon with mysql-test-run.pl cd /usr/mysql-test ; perl mysql-test-run.pl Please report any problems with the /usr/scripts/mysqlbug script!
Käivitamiseks
# mysqld_multi start 1 # mysqld_multi stop 1
Ülevaate saamiseks sobib öelda
# mysqld_multi report Reporting MySQL servers MySQL server from group: mysqld1 is running MySQL server from group: mysqld2 is running
Kasulikud lisamaterjalid
Päringute logimine
Tundub, et muudatusi logimist kontrollivad sellised direktiivid
# cat /etc/mysql/my.cnf .. log_bin = /var/log/mysql/mysql-bin.log epire_logs_days = 10 max_binlog_size = 100M ...
Tulemusena tekivad sarnased failid
# ls -ld /var/log/mysql/* -rw-rw---- 1 mysql adm 412 Oct 28 22:35 /var/log/mysql/mysql-bin.000001 -rw-rw---- 1 mysql adm 32 Oct 28 22:33 /var/log/mysql/mysql-bin.index
ning muudatuste sisu saab esitada nt
# mysqlbinlog /var/log/mysql/mysql-bin.000001 | less
Märkused
- nn Debiani halduskasutajana saab baasi ühenduda öeldes samast arvutist
# mysql --defaults-file=/etc/mysql/debian.cnf
AppArmor kasutamine
- data kataloogi asukoha küsimine
mysql> select @@datadir; +-----------------+ | @@datadir | +-----------------+ | /var/lib/mysql/ | +-----------------+ 1 row in set (0.00 sec)
Kasulikud lisamaterjalid
Kasulikud lisamaterjalid
- http://wiki.debian.org/MySql
- Rsnapshot - Varundamine LVM snapshotiga
