MySQL

Материал из RSU WiKi
Перейти к: навигация, поиск
Tower of babel.png外國 language!
В статье используется несколько языков. Необходимо использовать один. Совсем неплохо, если это будет русский.

MySQL (/mɑɪ ɛs kjuː ɛl/, «май-эс-кью-эл», жарг. мускул) — свободная система управления базами данных (СУБД). MySQL является собственностью компании Oracle Corporation, получившей её вместе с поглощённой Sun Microsystems, осуществляющей разработку и поддержку приложения. Распространяется под GNU General Public License или под собственной коммерческой лицензией. Помимо этого разработчики создают функциональность по заказу лицензионных пользователей, именно благодаря такому заказу почти в самых ранних версиях появился механизм репликации [1].

Содержание

Установка

zypper in mysql
chkconfig mysql on
service mysql start

Настройка удаленного доступа к MySQL

How to Enable Remote Access to MySQL

MySQL remote access is disabled by default, but with a few simple steps, you should be up and running with remote MySQL in just a few minutes. This is especially useful when working with multiple developers in an Agile project environment, so that you all use the same data set, and can focus on just writing the application.

Make note that I’m not focused on the “most secure” way for that you would probably want to do all of this through an ssh tunnel. However, this should be plenty secure for most developers, especially if you’re working with a dev server and not production which is what I’ve geared this post towards.

Login to SSH to edit remote MySQL config

First, we need to edit the mysql config file to accept and bind remote connections to your server. We do this by editing your my.conf file located on most unix systems at /etc/my.conf or /etc/mysql/my.conf. I’m going to hope and assume you know the basics to ssh into your remote server and vi or nano the conf file.

ssh root@yourserver.com
vi /etc/my.conf
Replace mysqld Defaults

You can either set this up as a new connection or override the default, in this case, I replaced the default connection with my own remote connection settings. Keep in mind if you want to be creative and bind to localhost without interfering with other settings or services you can bind to 0.0.0.0 or another alias.

[mysqld]
bind-address    = 255.112.324.12 НЕ НАДО ТАК ДЕЛАТЬ! ПЛОХАЯ СТАТЬЯ НЕГОДНАЯ СОВСЕМ!
port            = 3306
user		= mysql
pid-file        = /var/run/mysqld/mysqld.pid
socket          = /var/run/mysqld/mysqld.sock
basedir         = /usr
datadir         = /var/lib/mysql
tmpdir          = /tmp
language        = /usr/share/mysql/English
# skip-networking
# skip-external-locking

After your done editing, just save and close the file and restart mysql services:

# Ubuntu
service mysql restart
# Other Unix
/sbin/init.d/mysql restart
# Test that your connection is allowed with telnet on your local machine:
telnet 255.112.324.12 3306

Granting Remote Access to MySQL Users

Now we’ve created our remote config for MySQL, we have to grant access to this server to other machines.

mysql -uroot -pMyPass
CREATE DATABASE mydb;
# Grant permission to root from any host:
GRANT ALL ON mydb.* TO root@'%' IDENTIFIED BY 'MyPASSWORD';

Open Up MySQL Remote Ports

Now that our user has been granted access from any host, all thats left is to make sure our OS will allow connections to the default MySQL port

/sbin/iptables -A INPUT -i eth0 -p tcp --destination-port 3306 -j ACCEPT

And now we should be able to login to our server from our local machine:

mysql -h255.112.324.12 -uroot -pMyPASSWORD

Создание БД

Юникод в БД

DROP DATABASE www;
CREATE DATABASE www CHARACTER SET utf8 COLLATE utf8_general_ci;
USE www;
set CHARACTER SET utf8;

Импорт данных из текстового файла

Была поставлена задача в таблицу из 2 колонок int auto_increment и varchar(255):

CREATE TABLE IF NOT EXISTS `exampleTable` (
  `identificator` int(11) NOT NULL auto_increment,
  `textField` varchar(255) NOT NULL default '',
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=4;

добавить данные, хранящиеся в виде столбца в текстовом файле.

значение №1
значение №2
...

В статье про mysqlimport [2] в комментарии было найдено решение проблемы со значениями с столбце с параметром auto_increment:

"Keep in mind that your imported text file should have some value for empty fields. I regularly build tables using msqlimport to import tab-delimited text files. My tables contain integer fields, some of which are auto_incremented and some are not. MYSQL will let you represent empty fields as null text strings, i.e., two tab characters back-to-back, but I found this increments the warning count. To solve this problem you must use some value for empty fields. Since auto_increment fields use 0 or NULL, one would think, incorrectly, that you could use 0 or \N to represent a null value in the import text file. You must 0 for an auto_increment field. Using \N increments the warning count. You should use \N for other numeric fields where you want a null value. This problem is especially perplexing because of MySQL's inability to report the text of a warning. It only reports a warning count."

В нашем случае достаточно добавить столбик из одних нулей, соответствующий по порядку полю с параметром auto_increment:

awk -F \t '{print "0\t"$1}' tableName.txt > tmp; mv tmp tableName.txt

В результате получим нужный формат:

0\tзначение №1
0\tзначение №2
...

Следующая команда проиводит добавление данных непосредственно в таблицу. Необходимо что бы пользователю, указанному в ключе userName, была дана привелегия "file" [3]:

mysqlimport -u userName -h example.com dbName tableName.txt

Резервное копирование

Основная статья: Резервное_копирование#MySQL

Как в bash-скрипте получить список всех БД[4]:

databases=(`echo 'show databases;' | mysql -u <user> --password='<pwd>' | grep -v ^Database$`)

Обслуживание

Оптимизация таблиц на сервере:

mysqlcheck -o -A -p

Ремонт всех таблиц:

mysqlcheck --auto-repair --all-databases

Все вместе:

mysqlcheck --optimize --all-databases --auto-repair --password

Оптимизация и тюнинг

Повышаем производительность сервера [5] [6]

cd /usr/local/src/
wget http://day32.com/MySQL/tuning-primer.sh
chmod u+x tuning-primer.sh
./tuning-primer.sh

Смотрим на рекомендации, которые выдал скрипт.

Заодно включаем thread_cache_size [7]

Конвертируем БД из MyISAM в InnoDB

mysql -p -e "show tables in db_name;" | tail --lines=+2 | xargs -i echo "ALTER TABLE {} ENGINE=INNODB;" > /tmp/alter_table.sql
mysql --database=db_name -p -f -v < /tmp/alter_table.sql
rm /tmp/alter_table.sql

my.cnf

На наших серверах используются такие параметры для повышения производительности:

# The MySQL server
[mysqld]
port          = 3306
socket       = /var/lib/mysql/mysql.sock
skip-locking
key_buffer = 256M
max_allowed_packet = 1M
table_cache = 8192
sort_buffer_size = 512K
net_buffer_length = 8K
read_buffer_size = 256K
read_rnd_buffer_size = 512K
myisam_sort_buffer_size = 8M
query_cache_size = 64M
open_files_limit = 8192
max_heap_table_size = 64M
tmp_table_size = 64M
thread_cache_size = 8

Приложения

Удалить все таблицы из БД

#!/bin/bash
MUSER="$1"
MPASS="$2"
MDB="$3"
 
# Detect paths
MYSQL=$(which mysql)
AWK=$(which awk)
GREP=$(which grep)
 
if [ $# -ne 3 ]
then
	echo "Usage: $0 {MySQL-User-Name} {MySQL-User-Password} {MySQL-Database-Name}"
	echo "Drops all tables from a MySQL"
	exit 1
fi
 
TABLES=$($MYSQL -u $MUSER -p$MPASS $MDB -e 'show tables' | $AWK '{ print $1}' | $GREP -v '^Tables' )
 
for t in $TABLES
do
	echo "Deleting $t table from $MDB database..."
	$MYSQL -u $MUSER -p$MPASS $MDB -e "drop table $t"
done

Squid MySQL Authentication program

Simple Perl-script for authorizing users from mysql database on squid proxy-server:

#!/usr/bin/perl
use DBI;
use IO::Handle;
 
$dbh = DBI->connect("DBI:mysql:db-name:example.com","db-user","db-pass");
 
while ($answer = <STDIN>) {
    chomp $answer;
 
    @personal = split(/ /, $answer);
 
    $sql = "select login from users where login='$personal[0]' and password='$personal[1]'";
    $sth = $dbh->prepare($sql);
    $sth->execute or die "SQL Error: $DBI::errstr\n";
    $result = $sth->fetchrow_array();
 
    if ($result) {
        print "OK\n";
    } else {
        print "ERR\n";
 
    }
    IO::Handle::flush(STDOUT);
}

MySQL Database size

#!/bin/bash
 
PROGNAME=`basename $0`
 
function usage()
{
    echo "Usage: $PROGNAME dbname" 1>&2
    exit 0
}
 
# Cheking if all needed programs are installed
for PROGZ in mysql egrep awk; do
    WPROGZ=`which $PROGZ`
    if test $? -ne 0; then
        # Error message is printed by 'which'
        exit 1
    fi
done
 
# Check for correct number of parameters
test $# -gt 0 || usage;
 
mysql -p -u root -D $1 -e "show table status\G" \
 | egrep "(Index|Data)_length" \
 | awk 'BEGIN { rsum = 0 } { rsum += $2 } END { print rsum }'

Устранение неполадок

Если phpMyAdmin перестает присоединяться при полном молчании, проверьте лог /var/log/mysqld.log на наличие следующей ошибки:

Number of processes running now: 0
120403 15:08:22  mysqld restarted
120403 15:08:22  mysqld restarted
120403 15:08:22  InnoDB: Started; log sequence number 0 4382027
InnoDB: Unable to lock ./ibdata1, error: 11
InnoDB: Check that you do not already have another mysqld process
InnoDB: using the same InnoDB data or log files.
120403 15:08:22  InnoDB: Retrying to lock the first data file
120403 15:08:22 [ERROR] /usr/sbin/mysqld: Error writing file '/var/lib/mysql/mysqld.pid' (Errcode: 28)
120403 15:08:22 [ERROR] Can't start server: can't create PID file: No space left on device

Number of processes running now: 1
mysqld process hanging, pid 29073 - killed

Значит кончилось место на жестком диске. Воспользуйтесь командами df -h и COMMAND для поиска папок с неактуальными резервными копиями и пр.

Примечания

  1. MySQL. Материал из Википедии — свободной энциклопедии
  2. MySQL 5.0 Reference Manual :: 4.5.5 mysqlimport — A Data Import Program
  3. Errors Using mysqlimport to Import Data Into MySQL
  4. MySQL 5.1 Reference Manual :: 4.5.4 mysqldump — A Database Backup Program
  5. drupal.org: Tools, tips and links on optimizing mySQL
  6. Drupal в рунете: Анализ производительности базы данных MySQL
  7. Тюнинг MySQL — thread_cache_size

См. также

Ссылки

Личные инструменты
Пространства имён

Варианты
Действия
Навигация
Инструменты