Whoever Resembles a People is One of Them. He is not one of us who resembles other than us,
nor who resembles the Jews nor the Christians.(jami-at-tirmidhi-vol-5-ahadith-2695)
O you who believe (who wish to reach ALLAH C.C. before death)! Do not take my and
your enemies for friends!
And although they have denied what has come to you
from the Truth, you still love them (treating them as your friends).
If you go forth to struggle hard in My Way, seeking My Pleasure, (then why) you manifest
love to them and share with them your secrets.
And I know what you conceal and what you manifest. And whoever of you does that, then he
indeed has gone astray from the right Way. 60/Al-Mumtahanah-1
They are driving out the Messenger and yourselves from your land because you
believe in ALLAH C.C., your Lord.
Recite (read and explain to others) that which has been revealed to you of the Book
and keep up the Prayer.
Surely the Prayer keeps away from Al-Fahsh (to do what ALLAH C.C. prohibits) and Al-Munkar
(to deny what ALLAH C.C. commands).
Whoever obeys me, obeys ALLAH C.C., and whoever disobeys me, disobeys ALLAH C.C.
Whoever obeys the ruler, obeys me, and whoever disobeys the ruler, disobeys me. " (Sahih)

MySQL Kurulumu ve Ayarları



Güncel mysql versiyonunu http://www.mysql.com/downloads/mysql#downloads linkinden indirebilirsiniz.

/etc/hosts --dosyasında sunucunuzun hostname ve ip sinin yazması önemli, Eğer cluster aktif/pasif vb yapılar kurulacaksa tüm sunucuların hostname ve ip leri girilmesi gerekir aksi halde çalışmayı engeller.

127.0.0.1 localhost.localdomain localhost
192.168.56.101 db01.local.domain db01


RPM Paketlerinden MySQL kurulumu

mount -t iso9660 /dev/scd0 /media/cd

cd deki rpm paketlerini yum repositroy sinde göstermek için

 

vim /etc/yum.repos.d/rhel.repo[rhel]
name=RHEL
baseurl=file:///media/cd/Server>
enabled=1
gpgcheck=0yum install mysql-server mysql

tar.gz paketlerinden kurulum

mkdir -p /mysqlgroupadd mysql
useradd -d /mysql -s /bin/bash -g mysql mysql
passwd mysql -- password should be unique to this MySQL installation
-- a different password to the production environmentcd /mysql
tar -xzvf mysql-5.5.28-linux2.6-x86_64.tar.gz
ln -s mysql-5.5.28-linux2.6-x86_64 mysqlcd mysql
chown -R root .
chown -R mysql data
chgrp -R mysql .

"my.cnf" dosyasını kendimize göre düzenleyip. /etc/ dizinine kopyalıyoruz.
önceki sürümden var ise "cp /etc/my.cnf /etc/my.cnf_onceki" şeklinde kopyalıyorum.

Öncesinde mysql kurulum var ise;

mv /usr/bin/mysql /usr/bin/mysql.old --eski sürüm var ise
ln -s /mysql/mysql/bin/mysql /usr/bin/mysql
cp support-files/mysql.server /etc/init.d/mysql
chmod 755 /etc/init.d/mysql
chkconfig --add mysql
chkconfig --level 345 mysql on

Başlatalım ;

/mysql/scripts/mysql_install_db --user=mysql
/mysql/bin/mysqld_safe --user=mysql &

Düşen Logları izlemek için ;

tail -f /mysql/mysql/data/db01.local.domain.err

 

 

/mysql/mysql/bin/mysqladmin -u root password '**SIFRE**'
/mysql/mysql/bin/mysqladmin -u root -h host_isminiz password '**SIFRE**' --!!!!!! host adini değiştirmeyi unutmayın.

Test edelim ;

mysql -uroot -p**PASSWORD**


Mevcut veri tabanlarını görelim ;

show databases ;

Veri tabanı oluşturalım ;

CREATE DATABASE exampledb DEFAULT CHARACTER SET utf8;

Kullanıcı oluşturalım

CREATE USER 'exampledb_user'@'localhost' IDENTIFIED BY 'password';

Hak Verelim;

GRANT ALL PRIVILEGES ON * . * TO 'exampledb_user'@'localhost' IDENTIFIED BY 'password' WITH GRANT OPTION MAX_QUERIES_PER_HOUR 0 MAX_CONNECTIONS_PER_HOUR 0 MAX_UPDATES_PER_HOUR 0 MAX_USER_CONNECTIONS 0 ;

Sadece oluşturduğumuz veri tabanında hak veriyoruz ;

GRANT ALL PRIVILEGES ON `exampledb` . * TO 'exampledb_user'@'localhost';

 

 

show databases ;
use exampledb;

Her Yerden Bağlanılabilmek için

GRANT ALL PRIVILEGES ON *.* TO 'root'@'%.%' IDENTIFIED BY '**PASSWORD**';

Sadece Belirli Yerlerden bağlabilecek konular

GRANT ALL PRIVILEGES ON *.* TO 'root'@'192.168.7.93' IDENTIFIED BY '**PASSWORD**';
GRANT ALL PRIVILEGES ON *.* TO 'root'@'host_name' IDENTIFIED BY '**PASSWORD**';

 


innodb_data_home_dir :Datafile ların default lokasyonu
innodb_data_file_path :datafile isimleri ve boyutlarının bilgisi (eğer innodb_data_home_dir değer girilmedi ise pahthleri ile )
örnek ;
innodb_data_file_path = ibdata1:50M;ibdata2:50M;ibdata3:50M:autoextend:max:100M
3 adet datafile var boyutları 50 MB datafile 3 max 100 MB olabilir.
Önemli : Mysql e datafile eklemek için veritabanını kapatmak gerekiyor . o yüzden yazılımcılardan ne kadar yere ihtiyaç olacağını ve yıllık planlamanın yapılması çok önemli.
datafile ekleneceği zaman ibdata3 un tam boyutu

[root@db01 ~]# ll /mysql/mysql/data/ibdata3
-rw-rw---- 1 mysql mysql 47185920 Oct 1 15:06 /mysql/mysql/data/ibdata3

52428800 değeri iki kez 1024 bölünüp MB boyutu bulunur diyelim ki 45 MB da iken fark ettik ve datafile eklemek istedik bu durumda aşağıdaki gibi yapabiliriz.

innodb_data_file_path = ibdata1:50M;ibdata2:50M;ibdata3:45M;ibdata4:200M;:autoextend:max:500M

3. datafile 45 mb olarak yazdık ve 4. yü 200mb olarak ekledik ve max boyutu 500MB verdik.
Data file ları farklı lokayonlarda konumlandırmak istenirse /full/path/ibdata1:50M şeklinde yazılması lazım.


Detaylı bilgi için;
http://dev.mysql.com/doc/refman/5.1/en/using-system-variables.html

mysql> SHOW VARIABLES;

 

mysql>SHOW VARIABLES LIKE 'max_join_size';
mysql>SHOW SESSION VARIABLES LIKE 'max_join_size';

 

mysql>SHOW VARIABLES LIKE '%size%';
mysql>SHOW GLOBAL VARIABLES LIKE '%size%';

 

mysql>SET sort_buffer_size=10000;
mysql>SET @@local.sort_buffer_size=10000;
mysql>SET GLOBAL sort_buffer_size=1000000, SESSION sort_buffer_size=1000000;
mysql>SET @@sort_buffer_size=1000000;
mysql>SET @@global.sort_buffer_size=1000000, @@local.sort_buffer_size=1000000;
mysql> SET GLOBAL max_allowed_packet=16M;
mysql> SET GLOBAL max_allowed_packet=16*1024*1024;
mysql>SET max_join_size=DEFAULT;
mysql>SET @@session.max_join_size=@@global.max_join_size;

 

 

mysql>SELECT @@global.sql_mode, @@session.sql_mode, @@sql_mode;

 

shell> mysql --max_allowed_packet=16M
shell> mysql --max_allowed_packet=16*1024*1024


Processes lerden mysql in izlenmesi

#ps -ef|grep mysql

root 2471 1 0 07:24 ? 00:00:00 /bin/sh /mysql/mysql/bin/mysqld_safe --log-queries-not-using-indexes --log-slave-updates --datadir=/mysql/mysql/data --pid-file=/mysql/mysql/data/db01.local.domain.pid

mysql 3303 2471 0 07:24 ? 00:00:03 /mysql/mysql/bin/mysqld --basedir=/mysql/mysql --datadir=/mysql/mysql/data --plugin-dir=/mysql/mysql/lib/plugin --user=mysql --log-queries-not-using-indexes --log-slave-updates --log-error=/mysql/mysql/data/db01.local.domain.err --open-files-limit=8192 --pid-file=/mysql/mysql/data/db01.local.domain.pid --socket=/tmp/mysql.sock --port=3306

root 3730 3699 0 07:25 pts/1 00:00:00 mysql -u root -p

root 3813 3760 0 07:48 pts/2 00:00:00 grep mysql
Mysql in çalışıp çalışmadığı çalışıyorsa hangi portu dinlediği

#netstat -ntlp -- 3306

Active Internet connections (only servers)

Proto Recv-Q Send-Q Local Address Foreign Address State PID/Program name

tcp 0 0 0.0.0.0:963 0.0.0.0:* LISTEN 2053/rpc.statd

tcp 0 0 0.0.0.0:3306 0.0.0.0:* LISTEN 3303/mysqld

Log izleme

tail -f /mysql/mysql/data/db01.local.domain.err

Status information

SELECT table_schema "Data Base Name", sum( data_length + index_length) / 1024 / 1024
"Data Base Size in MB" FROM information_schema.TABLES GROUP BY table_schema ;

 

 

SELECT table_name, table_row
s, data_length, index_length,
round(((data_length + index_length) / 1024 / 1024),2) "Size in MB"
FROM information_schema.TABLES WHERE table_schema = 'PFM_METERING';

 

show databases ;
use sampledb

 

CREATE TABLE sample_table (
`name` varchar(255) default NULL,
`surname` varchar(255) default NULL,
`city` varchar(50) default NULL,
`country` varchar(50) default NULL
) ;

 

 

INSERT INTO `sample_table` (`name`,`surname`,`city`,`country`) VALUES ('Jin','Eaton','Clarksville','Mayotte');
INSERT INTO `sample_table` (`name`,`surname`,`city`,`country`) VALUES ('Coby','Long','Ames','United States');
INSERT INTO `sample_table` (`name`,`surname`,`city`,`country`) VALUES ('Phoebe','Gay','Cedarburg','Sweden');
INSERT INTO `sample_table` (`name`,`surname`,`city`,`country`) VALUES ('Blair','Dickson','Carson City','Oman');
INSERT INTO `sample_table` (`name`,`surname`,`city`,`country`) VALUES ('Yoshio','Hogan','Sacramento','Tonga');
INSERT INTO `sample_table` (`name`,`surname`,`city`,`country`) VALUES ('Brent','Zamora','Oil City','Bouvet Island');
INSERT INTO `sample_table` (`name`,`surname`,`city`,`country`) VALUES ('Xavier','Cooley','Sheridan','Barbados');
INSERT INTO `sample_table` (`name`,`surname`,`city`,`country`) VALUES ('Keegan','Schneider','White Plains','Algeria');
INSERT INTO `sample_table` (`name`,`surname`,`city`,`country`) VALUES ('Baker','Carlson','Monterey Park','France');
INSERT INTO `sample_table` (`name`,`surname`,`city`,`country`) VALUES ('Myra','Mcintyre','Duarte','Japan');
INSERT INTO `sample_table` (`name`,`surname`,`city`,`country`) VALUES ('Vaughan','Harding','Fitchburg','Antigua and Barbuda');
INSERT INTO `sample_table` (`name`,`surname`,`city`,`country`) VALUES ('Maggie','Holloway','Plano','Namibia');
INSERT INTO `sample_table` (`name`,`surname`,`city`,`country`) VALUES ('Solomon','Johns','Lock Haven','Western Sahara');
INSERT INTO `sample_table` (`name`,`surname`,`city`,`country`) VALUES ('Jeremy','Castro','Brea','Japan');
INSERT INTO `sample_table` (`name`,`surname`,`city`,`country`) VALUES ('Gloria','Walls','Madison','Togo');
INSERT INTO `sample_table` (`name`,`surname`,`city`,`country`) VALUES ('Price','Keller','Trenton','Swaziland');
INSERT INTO `sample_table` (`name`,`surname`,`city`,`country`) VALUES ('Mara','Johnston','Cedar Falls','Georgia');
INSERT INTO `sample_table` (`name`,`surname`,`city`,`country`) VALUES ('Aurelia','Hardin','Rome','Albania');
INSERT INTO `sample_table` (`name`,`surname`,`city`,`country`) VALUES ('Quon','Bender','Augusta','Hungary');
INSERT INTO `sample_table` (`name`,`surname`,`city`,`country`) VALUES ('Jorden','Rasmussen','Aguadilla','Guam');
INSERT INTO `sample_table` (`name`,`surname`,`city`,`country`) VALUES ('Dacey','Knox','Grand Island','Pitcairn');
INSERT INTO `sample_table` (`name`,`surname`,`city`,`country`) VALUES ('Merrill','Hudson','Champaign','Anguilla');
INSERT INTO `sample_table` (`name`,`surname`,`city`,`country`) VALUES ('Hayfa','Noble','Utica','Cambodia');
INSERT INTO `sample_table` (`name`,`surname`,`city`,`country`) VALUES ('Carlos','Sanford','Aberdeen','Singapore');
INSERT INTO `sample_table` (`name`,`surname`,`city`,`country`) VALUES ('Elton','Carlson','Burbank','Guinea-bissau');
INSERT INTO `sample_table` (`name`,`surname`,`city`,`country`) VALUES ('Keely','Summers','Helena','French Guiana');
INSERT INTO `sample_table` (`name`,`surname`,`city`,`country`) VALUES ('Wallace','Hodge','Pittsburgh','Jamaica');
INSERT INTO `sample_table` (`name`,`surname`,`city`,`country`) VALUES ('Chaim','Patel','Sandy','Iceland');
INSERT INTO `sample_table` (`name`,`surname`,`city`,`country`) VALUES ('Colt','Carney','Brockton','Togo');
INSERT INTO `sample_table` (`name`,`surname`,`city`,`country`) VALUES ('Lila','Durham','Uniontown','Yemen');
INSERT INTO `sample_table` (`name`,`surname`,`city`,`country`) VALUES ('Velma','Scott','Casper','Benin');
INSERT INTO `sample_table` (`name`,`surname`,`city`,`country`) VALUES ('Maris','Cantu','Thousand Oaks','United Arab Emirates');
INSERT INTO `sample_table` (`name`,`surname`,`city`,`country`) VALUES ('Edward','Alvarez','Fort Worth','Somalia');
INSERT INTO `sample_table` (`name`,`surname`,`city`,`country`) VALUES ('Tatum','Oneil','Buena Park','Svalbard and Jan Mayen');

 

insert into tablo_deneme select * from sample_table ;


MS sistemde

mysqldump.exe -u kullanıcı_adı -p veritabanı_Adı > dump_dosya_ADI.sql

Linux sıkıştırarak yedekleme

mysqldump -u kullanıcı_adı -p veritabanı_Adı gzip -9 > dump_dosya_ADI.sql.gz

Birden fazla vt nin yedeği

mysqldump -u root -p --databases veritabanı_Adı01 veritabanı_Adı02 veritabanı_Adı03 > MY_VT_Yedek.sql

Hepsini yedeklemek isterseniz

mysqldump -u root -p --all-databases > MY_VT_Yedek.sql

Tabloların yedeği alınmak istenirse

mysqldump -u kullanıcı_ismi -p veritabanı_adı tablo_adı01 tablo_adı01

şifre sorulması istenmezse

mysqldump.exe --user=kullanıcı_adı --password=şifre veritabanı_ADI > DumpDosyasinin_ADI.sql

Yedekten geri dönüş

mysql.exe-u kullanıcı_adı-restore_Edilecek_veritabanı_ADI > DumpDosyasinin_ADI.sql

not : büyük vt lerde max_allowed_packet= 1G parametresinin değiştirilmesi gerekebilir (unix de my.cnf MS'de my.ini dosyasında [mysqld[ nin altında).

Copyright © 2015 Sysdba All Rights Reserved Web Designer