Archive

Archive for İ

Alter Table Charset and Collation

DELIMITER $$

CREATE DEFINER=`root`@`127.0.0.1` PROCEDURE `multiple_alter_table_charset`()
BEGIN
	declare v_table_name varchar(30);
	declare v_last_row_fetched int default 0;
	declare v_table_schema varchar(30);
	
	declare cursor1 cursor for
	select table_schema,table_name from information_schema.tables
	where table_schema not in ('mysql' , 'performance_schema', 'information_schema')
	and table_type = 'base table'
	and table_collation = 'latin1_swedish_ci';

	declare continue handler for not found set v_last_row_fetched=1;

	set v_last_row_fetched=0;
	open cursor1;
	cursor_loop: loop
	fetch cursor1 into v_table_schema,v_table_name;
			if v_last_row_fetched=1 then
					leave cursor_loop;
			end if;
			
			set @sql_v=concat('alter table ',v_table_schema,'.',v_table_name,' CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci');
			prepare stmt from @sql_v;
			EXECUTE stmt;
			deallocate prepare stmt;
	end loop cursor_loop;
	close cursor1;
	set v_last_row_fetched=0;
	
END$$

Procedure bütün latin1-ləri tapır və hamısını UTF8-ə çevirir

*Diqqət!!! Production-da istifadə etməzdən əvvəl hər hansı bir ERROR əleyhinə test-də sınamaq məsləhətdir.*

Kateqoriyalar: My Stored Routines

Alter Table Engine procedure

delimiter $$

CREATE PROCEDURE `multiple_alter`()
begin 
	declare v_table_schema varchar(30);
	declare v_table_name varchar(30);
	declare v_last_row_fetched int default 0;
	
	declare cursor1 cursor for
	select 
        a.table_schema, a.table_name from
        information_schema.tables as a
        where a.engine = 'MyISAM'
	and a.table_schema not in ('mysql' , 'information_schema','performance_schema');

	declare continue handler for not found set v_last_row_fetched=1;

	set v_last_row_fetched=0;
	open cursor1;
	cursor_loop: loop
	fetch cursor1 into v_table_schema,v_table_name;
			if v_last_row_fetched=1 then
					leave cursor_loop;
			end if;
			
			set @sql_v=concat('alter table ',v_table_schema,'.',v_table_name,' engine=innodb');
			prepare stmt from @sql_v;
			EXECUTE stmt;
			deallocate prepare stmt;
	end loop cursor_loop;
	close cursor1;
	set v_last_row_fetched=0;
	
end$$

Yuxarıdakı procedure database-də olan bütün MyİSAM cədvəlləri tapır və hamısını İnnoDB-yə çevirir.

Diqqət!!! Production-da istifadə etməzdən əvvəl hər hansı bir ERROR əleyhinə test-də sınamaq məsləhətdir.

Kateqoriyalar: My Stored Routines

Starting and Stopping MySQL Server

Start stop məsələsi elə də çətin olmamalıdır. Amma Start edib sonra stop edə bilməyəndə maraqlı olur 🙂
Birinci üsul:

-- START
[root@sh ~]# systemctl start mysqld.service

-- Shutdown
[root@sh ~]# systemctl stop mysqld.service

Normal qaydada işləyir və connection da gedir.

İkinci üsul:

-- START
[root@sh ~]# service mysqld start
Redirecting to /bin/systemctl start  mysqld.service

-- Shutdown
[root@sh ~]# service mysqld stop
Redirecting to /bin/systemctl stop  mysqld.service

Yenə də normal qaydada connect olmaq olur.

Üçüncü üsul:
mysqld_safe shell script-i var ki bu script çalışdırıldığında mysqld-ni avtomatik olaraq çağırır və server-i start edir. Location: /usr/bin
Sınayaq:

[root@sh ~]# cd /usr/bin
[root@sh bin]# ./mysqld_safe
130112 15:46:37 mysqld_safe Logging to '/var/log/mysqld.log'.
130112 15:46:37 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
.
.
.

Biraz gözlədim ki birdən nəsə olar amma sadəcə bu çəkildə gözləyir. Sən demə artıq mysqld_safe MySQL-i artıq start edib Və connection da gedir:

[root@sh ~]# mysql -u monty -p
Enter password: 
Welcome to the MySQL monitor.

İndi isə yuxarıda göstərilən qaydaların biri ilə Shutdown edək:

[root@sh ~]# systemctl stop mysqld.service

Xeyri yoxdu Shutdown olmur…Connection gedəcək adi qaydada

[root@sh ~]# mysql -u monty -p
Enter password: 
Welcome to the MySQL monitor.

Biraz axtarışdan sonra bu halda Shutdown etməyin 2 yolu var imiş:
1. Manual olaraq linux-da root kimi mysqld-ni kill etmək
2. mysqladmin shutdown

1.Killing
Running mysql proseslərə nəzər yetirdikdə:

[root@sh ~]# ps -ef|grep -i mysql
1000      9530     1  0 15:22 ?        00:00:02 gedit /var/log/mysqld.log
root     11573 10164  0 15:46 pts/4    00:00:00 /bin/sh ./mysqld_safe
mysql    12087 11573  0 15:46 pts/4    00:00:05 /usr/libexec/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib/mysql/plugin --user=mysql --log-error=/var/log/mysqld.log --open-files-limit=65535 --pid-file=/var/run/mysqld/mysqld.pid --socket=/var/lib/mysql/mysql.sock
root     12518  8589  0 16:37 pts/1    00:00:00 grep --color=auto -i mysql

Bizə lazımdır PİD=12087 kill-lənsin 🙂

[root@sh ~]# kill 12087

Bundan sonra əvvəli mysqld_safe çağırılan pencərədə yeni yazı peyda olur:

[root@sh bin]# ./mysqld_safe
130112 15:46:37 mysqld_safe Logging to '/var/log/mysqld.log'.
130112 15:46:37 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
130112 16:42:15 mysqld_safe mysqld from pid file /var/run/mysqld/mysqld.pid ended

ENDED yazısı!
Sınamaq üçün connect olaq:

[root@sh ~]# mysql -u monty -p
Enter password: 
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2)

2. mysqladmin shutdown

Yenidən mysqld_safe-i işlədək

[root@sh bin]# ./mysqld_safe
130112 16:47:02 mysqld_safe Logging to '/var/log/mysqld.log'.
130112 16:47:02 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
.
.
.

indi isə shutdown:

[root@sh ~]# mysqladmin -u root -p shutdown
Enter password:

Və sonda yenə ENDED görəcik

[root@sh bin]# ./mysqld_safe
130112 16:52:58 mysqld_safe Logging to '/var/log/mysqld.log'.
130112 16:52:58 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
130112 16:53:17 mysqld_safe mysqld from pid file /var/run/mysqld/mysqld.pid ended

Təşəkkürlər 🙂

MySQL grant table and initial accounts

MySQL installation-la bərabər mysql,information_schema database-ləri gəlir.
*mysql bu yazıda server yox məhz mysql schema(database) adıdır*
mysql-də user cədvəli var ki, orada MySQL(server) user-lərin access privilege-ləri və.s məlumatları yerləşir.
mysql full table list:

mysql> show tables;
+---------------------------+
| Tables_in_mysql           |
+---------------------------+
| columns_priv              |
| db                        |
| event                     |
| func                      |
| general_log               |
| help_category             |
| help_keyword              |
| help_relation             |
| help_topic                |
| host                      |
| ndb_binlog_index          |
| plugin                    |
| proc                      |
| procs_priv                |
| proxies_priv              |
| servers                   |
| slow_log                  |
| tables_priv               |
| time_zone                 |
| time_zone_leap_second     |
| time_zone_name            |
| time_zone_transition      |
| time_zone_transition_type |
| user                      |
+---------------------------+
24 rows in set (0.00 sec)

Elə bu haqda oxuyurdum ki maraqlı bir şey aşkarlamışam…Sən demə parolsuz root user-lər və ananonim user-lər mövcud imiş. Təbii ki bu təhlükəli haldı.
Beynəlxalq məsləhətə əsasən biz bütün parolsuz root-lara parol verəcik və ananonim user-ləri siləcik…

Yoxlamaq üçün bir neçə connection yerinə yetirək.
root connection no password

[root@sh ~]# mysql -u root --host=127.0.0.1
Welcome to the MySQL monitor.
.
.
.
mysql> show grants;
+---------------------------------------------------------------------+
| Grants for root@127.0.0.1                                           |
+---------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'127.0.0.1' WITH GRANT OPTION |
+---------------------------------------------------------------------+
1 row in set (0.00 sec)

Nə yaxşı 🙂

Anonym user connection:

[root@sh ~]# mysql -u --host=localhost
Welcome to the MySQL monitor.
.
.
.
mysql> show grants;
+--------------------------------------+
| Grants for @localhost                |
+--------------------------------------+
| GRANT USAGE ON *.* TO ''@'localhost' |
+--------------------------------------+
1 row in set (0.00 sec)

The USAGE privilege specifier stands for “no privileges.”

Həqiqətən də anonim user-lə heç bir şey etmək olmur.

mysql> use world;
ERROR 1044 (42000): Access denied for user ''@'localhost' to database 'world'

Təcili olaraq bu yuxarıdakı 2 anonim user-i silirik:

mysql> DROP USER ''@'localhost';
Query OK, 0 rows affected (0.03 sec)

mysql> DROP USER ''@'sh.rzayev';
Query OK, 0 rows affected (0.11 sec)

Bəli silindilər:

Daha sonra parolsuz root-lara parol verək. Tək-tək fərqli parollar və yaxud toplu halda eyni parolu vermək olur:
Tək-tək:

mysql> SET PASSWORD FOR 'root'@'127.0.0.1' = PASSWORD('yeni_parol');
mysql> SET PASSWORD FOR 'root'@'::1' = PASSWORD('yeni_parol');
mysql> SET PASSWORD FOR 'root'@'host_name' = PASSWORD('yeni_parol');

Toplu halda:

mysql> UPDATE mysql.user SET Password = PASSWORD('yeni_parol')
    ->     WHERE User = 'root';
mysql> FLUSH PRIVILEGES;

FLUSH məcbur edir ki server grant-ları yenidən oxusun. Əksi təqdirdə yeni parollar təsirsiz qalır.
Yeni password-lər verildi:

Təşəkkürlər 🙂

MySQL table optimization

Əgər şikayət gəlirsə ki, `mənim sorğum zəif gedir` dərhal o sorğunu optimize etməyə çalışmaq beynəlxalq təcrübədə dəhşətli dərəcədə rədd edilir.
MySQL optimization ümumi olaraq 3 hissəyə
1. Server optimization
2. Schema\table optimization
3. Query optimization

Göstərildiyi kimi yuxarıdan aşağıya getdiyimiz halda nəzərə çarpacaq dərəcədə uğur əldə edə bilərik.

Table optimization üçün əsas olaraq:
ANALYZE TABLEOPTIMIZE TABLE istifadə olunur.
Hər iki komanda cədvəllər üçün vaxtaşırı istifadə olunmalıdır. Yəni 1 dəfə istifadə olundu vəssalamla olmur malesef.

1.ANALYZE TABLE
Deyək ki sizin 2 cədvəliniz var və bu cədvələ tez-tez insert-lər olunur, index-lər əlavə olunur drop olunur və.s
From documentation:
ANALYZE TABLE analyzes and stores the key distribution for a table
MySQL uses the stored key distribution to decide the order in which tables should be joined when you perform a join on something other than a constant. In addition, key distributions can be used when deciding which indexes to use for a specific table within a query.

Dokumentasiyadan belə aydın olur ki, qısa olaraq ANALYZE TABLE bizim join-lərimizi və index lookup-larımızı sürətləndirir. Əyani misal. City və Country cədvəllərini join edirik:

mysql> select country.name,group_concat(city.name) from country,city where country.code=city.countrycode group by country.name;
.
.
.
232 rows in set, 8 warnings (0.14 sec)

Vaxt 0.14 sec

ANALYZE TABLE-ın effektini nümayiş etdirmək üçün:

mysql> analyze table country,city;
+---------------+---------+----------+----------+
| Table         | Op      | Msg_type | Msg_text |
+---------------+---------+----------+----------+
| world.country | analyze | status   | OK       |
| world.city    | analyze | status   | OK       |
+---------------+---------+----------+----------+
2 rows in set (0.12 sec)

Və eyni join-li sorğunu bir daha işlədirik:

mysql> select country.name,group_concat(city.name) from country,city where country.code=city.countrycode group by country.name;
.
.
.
232 rows in set, 8 warnings (0.01 sec)

Vaxt 0.01 sec
🙂 Fərq var elə deyil mi?

2.OPTIMIZE TABLE

From Documentation:
Reorganizes the physical storage of table data and associated index data, to reduce storage space and improve I/O efficiency when accessing the table. The exact changes made to each table depend on the storage engine used by that table.
The table and indexes are reorganized, and disk space can be reclaimed for use by the operating system.

Coxsaylı insert, update, delete və.s etdikdən sonra OPTIMIZE TABLE işlədilməsi cədvəli yenidən qurur,index-ləri yenidən qaydaya salınır, memory və yaddaşdakı boşluqlar və.s tənzimlənir. Qısası cədvəlimizə yeni nəfəs gəlir.

Sınayaq:

mysql> select * from sales;
.
.
.
2500004 rows in set (12.57 sec)

Vaxt: 12 dəqiqə 57 saniyə

OPTIMIZE TABLE:

mysql> optimize table sales;
+----------------+----------+----------+-------------------------------------------------------------------+
| Table          | Op       | Msg_type | Msg_text                                                          |
+----------------+----------+----------+-------------------------------------------------------------------+
| mysqlspp.sales | optimize | note     | Table does not support optimize, doing recreate + analyze instead |
| mysqlspp.sales | optimize | status   | OK                                                                |
+----------------+----------+----------+-------------------------------------------------------------------+
2 rows in set (1 min 54.30 sec)

Və yenidən:

mysql> select * from sales;
.
.
.
2500004 rows in set (9.41 sec)

Vaxt: 9 dəqiqə 41 saniyə

Yenə də böyük fərq hiss olundu 🙂

Beynəlxalq məsləhət:
Ciddi şəkildə yüklənən və intensiv insert update delete olunan cədvəlləri ən azı həftədə 1 dəfə və yaxud intensiv select-lər gedirsə 3 gündən bir(select sürətinə təsir etmək məqsədilə) analyze və optimize etmək lazımdır.
Zəif işləyən (uzun çəkən) sorğuların optimizasiyasına server səviyyəsində başlamalı, daha sonra cədvəl və sorğunun özünə diqqəti cəlb etməli.

Bu və digər Table Maintenance komandalar haqqında daha ətraflı : Table Maintenance Statements

Təşəkkürlər 🙂

Statements That Cause an Implicit Commit

MySQL Transaction
Transactions and Locks
Transaction başlatdıqda transaction ərzində daxil edilən bütün komandalar commit\rollback-i gözləməli olur. Və o zamana qədər heç bir dəyişiklik baş vermir. Lakin elə komandalar var ki, transaction daxilində olmasından asılı olmayaraq implicit commit olur.
Bunlardan Bəzilərinə baxacıq.
1.ALTER TABLE

-- cədvəlin yaradılması
mysql> create table commit_test(id int) engine=innodb;

mysql> show create table commit_test\G
CREATE TABLE `commit_test` (
  `id` int(11)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

(1)Transaction başladaq:

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> alter table commit_test modify id int not null;
Query OK, 0 rows affected (0.43 sec)
Records: 0  Duplicates: 0  Warnings: 0

Məntiqlə düşünməli idik ki, commit olunmayınca table alter olunmalı deyildi. Amma digər bir connection-dan baxdıqda artıq alter-in implicit commit olduğunun şahidi oluruq:
(2)

CREATE TABLE `commit_test` (
  `id` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

2. DROP DATABASE

Ilk öncə schema-nı yaradaq:

mysql> create database for_drop;
Query OK, 1 row affected (0.11 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| backup_test        |
| cms                |
| data               |
| db1                |
| db_first           |
| exam_db            |
| for_drop    
.
.
18 rows in set (0.00 sec)

Transaction daxilində drop database-i işlədək:

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> drop database for_drop;
Query OK, 0 rows affected (0.29 sec)

mysql> rollback;
Query OK, 0 rows affected (0.00 sec)

rollback etməyimizə baxmayaraq…artıq drop olunub…
Digər connection-dan baxdıqda:

mysql> use for_drop;
ERROR 1049 (42000): Unknown database 'for_drop'

3. TRUNCATE TABLE

mysql> insert into commit_test() values(1),(2),(3),(4),(5),(6),(7),(8);
Query OK, 8 rows affected (0.16 sec)
Records: 8  Duplicates: 0  Warnings: 0

mysql> select * from commit_test;
+----+
| id |
+----+
|  1 |
|  2 |
|  3 |
|  4 |
|  5 |
|  6 |
|  7 |
|  8 |
+----+
8 rows in set (0.00 sec)

Transaction daxilində truncate edək:

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> truncate table commit_test;
Query OK, 0 rows affected (0.25 sec)

mysql> rollback;
Query OK, 0 rows affected (0.00 sec)

rollback-ə baxmayaraq truncate işlədi. digər connection-dan baxsaq:

mysql> select * from commit_test;
Empty set (0.00 sec)

4. RENAME TABLE

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> rename table commit_test to commit_test85;
Query OK, 0 rows affected (0.84 sec)

mysql> rollback;
Query OK, 0 rows affected (0.00 sec)

Digər connection-dan:

mysql> select * from commit_test;
ERROR 1146 (42S02): Table 'tr_blog.commit_test' doesn't exist
mysql> select * from commit_test85;
Empty set (0.00 sec)

Belə komandalar Sayca çoxdular hamısını yazmaq lazım deyil məncə.
İmplicit commit olan statements full list:
Statements That Cause an Implicit Commit

Təşəkkürlər 🙂