2022-04-13 m1gin 528
MySQL

Updata Using Row Number

SET @rownum:=0;
UPDATE tblarticle t1,
(SELECT @rownum := @rownum + 1 AS rank, id, title, idunit, idbook, idorder FROM tblarticle WHERE idunit=0 AND idbook=1 ORDER BY idorder) AS t2
SET t1.idorder=t2.rank
WHERE t1.id = t2.id;


Row Number

SET @rownum:=0; SELECT * FROM tblarticle t1,
(SELECT @rownum := @rownum + 1 AS rank, id, title, idunit, idbook, idorder FROM tblarticle WHERE idunit=19 ORDER BY idorder) AS t2
WHERE t1.id = t2.id AND t1.idunit=19;


Define UNIQUE Field
ALTER TABLE tblPage ADD UNIQUE (code);

---
ALTER TABLE tblPage ADD COLUMN id_site INT DEFAULT 0;
ALTER TABLE tblPage ADD COLUMN id_category INT DEFAULT 0;
--------

CREATE TABLE tblvote(
`id` INT NOT NULL AUTO_INCREMENT , idpoll INT DEFAULT 0,
response VARCHAR(255), userinfo VARCHAR(255), daterecord DATETIME,
feedback VARCHAR(255), extra VARCHAR(255), flag INT DEFAULT 0,
PRIMARY KEY(id)
)
ENGINE = MyISAM
DEFAULT CHARACTER SET = utf8 COLLATE utf8_general_ci;
-----------

CREATE TABLE tblcategory (
ID INT NOT NULL AUTO_INCREMENT, title VARCHAR(255), id_order INT DEFAULT 0, id_site INT DEFAULT 0, PRIMARY KEY(ID)
) ENGINE=MyISAM
CHARACTER SET utf8 COLLATE utf8_general_ci;
--
INSERT INTO tblcategory (title, id_site, id_order)
VALUES ("Podcast", 1, 20),
("Sağlık", 1, 100),
("Multimedya", 1, 10),
("Yabancı Dil", 1, 100),
("Genel", 1, 100),
("Eğitim", 1, 100),
("Beslenme ve Diyet", 1, 100),
("Kişisel Bakım", 1, 100)
--
UPDATE tblpage SET id_category=xx
WHERE Category="Podcast"
---
DROP TABLE tblTemplate;

Install MySQL on Ubuntu

sudo apt-get install mysql-server

sudo apt-get install mysql-workbench




MySQL NOTLARI

Veritabanı oluşturmak:

  • CREATE DATABASE db2;
  • CREATE DATABASE db3 DEFAULT CHARACTER SET utf8 DEFAULT COLLATE utf8_general_ci ;

Veritabanı silmek:

  • DROP DATABASE dbTemp;

Bir Sunucudaki Veritabanlarını Listelemek

  • SHOW DATABASES;

Tabloları Listelemek

  • SHOW TABLES;
  • SHOW FULL TABLES IN mb_mysql_1 LIKE "%med%";

Kullanım şekli:

SHOW [FULL] TABLES [{FROM | IN} db_name]

[LIKE 'pattern' | WHERE expr]

Alanları ve Tanımlamalarını Listelemek

  • SHOW COLUMNS FROM mb_mysql_1.tblmedia
  • SHOW FULL COLUMNS FROM mb_mysql_1.tblmedia LIKE “%u%”



  • Tablo oluşturmak:

CREATE TABLE `mb_mysql_1`.`tblmedia` (

`id` MEDIUMINT NOT NULL AUTO_INCREMENT,

`code` CHAR(6) NOT NULL,

`url` VARCHAR(255) NOT NULL,

`counter` INT NOT NULL,

`daterecord` DATE NOT NULL,

`dateupdate` DATE NOT NULL,

`flag` TINYINT UNSIGNED NOT NULL DEFAULT 0,

UNIQUE (`code`),

PRIMARY KEY (`id`)

)

ENGINE = MyISAM

CHARACTER SET utf8 COLLATE utf8_general_ci

COMMENT = 'media urls';

NOT: Tek tırnak kullanılmasa da oluyormuş.

CREATE TABLE tblyt (
id MEDIUMINT NOT NULL AUTO_INCREMENT,
id_video VARCHAR(20) NOT NULL,
counter INT,
dtrecord DATETIME DEFAULT CURRENT_TIMESTAMP,
dateupdate DATE NOT NULL,
flag TINYINT NOT NULL DEFAULT 0,
UNIQUE (id_video),
PRIMARY KEY (id)
)
ENGINE = MyISAM
CHARACTER SET utf8 COLLATE utf8_general_ci
COMMENT = 'yotube videos';

  • Sorgu sonucunu yeni tablo olarak oluşturmak:

CREATE TABLE “tbl2” SELECT * FROM “tbl1” WHERE id<22;



  • Benzer yapıya sahip bir tablo oluşturmak:

CREATE TABLE “tbl2” LIKE “tbl1”;

  • Veritabanındaki bir tabloyu bir başka veritabanına kopyalamak:

USE db2;

CREATE TABLE _tbl1_copy LIKE db1._tbl1;

INSERT INTO _tbl1_copy SELECT * FROM db1._tbl1;

Başka Bir Veritabanından Güncelleme Yapmak

UPDATE db1.tbl1, db2.tbl2

SET db1.tbl1.extra= db2.tbl2.extra

WHERE db1.tbl1.id=db2.tbl2.id AND db1.tbl1.id=11;

Tablo silmek:

DROP TABLE IF EXISTS `backup_db.backup_table`;

Tablodaki Alanı Düzenlemek:

ALTER TABLE `mb_mysql_1`.`tblmedia` CHANGE COLUMN `countcall` `counter` INTEGER NOT NULL;

Tablodaki bir alanı silmek:

ALTER TABLE tblpayment DROP COLUMN paid_amount

Tabloyu Yeniden Adlandırmak:

RENAME TABLE tblcurrent TO tblnew;

Bir Veritabanından Diğerine Tablo Taşımak

As long as two databases are on the same file system, you can use RENAME TABLE to move a table from one database to another:

RENAME TABLE current_db.tbl_name TO other_db.tbl_name;

RENAME TABLE db_common_mb.tbltmp TO db_sil.tblgmb



Tek Sorguda Kayıt Ekleme veya Güncelleme

'insert... if dublicate, update: increase the counter

INSERT INTO tblplayerror (url,counter,flag) VALUES ('url',1,0) ON DUPLICATE KEY UPDATE counter=counter+1;

Benzersiz özellikteki bir alana var olan bir değer eklenmek istendiğinde, UPDATE kısmı çalışır.




ALLOW REMOTE ACCESS to (local) MySQL SERVER (ve Kullanıcı İzinleri)

Yerel bilgisayara kurulan mysql serveri dış erişimlere açmak. Internetten (web sitesinden) yerel mysql servere erişmek.

In /etc/mysql/my.conf file, comment the line

(For Ubuntu16.04:/etc/mysql/mysql.conf.d/mysqld.cnf )

# bind-address= 127.0.0.1

or change it

bind-address= IPAddress

Then restart mysql:

sudo /etc/init.d/mysql restart

Open the terminal and:

mysql -u root -p

mysql>GRANT ALL PRIVILEGES ON mb_mysql_1.* TO m1@204.93.161.17 IDENTIFIED BY “apassword”;

or for all databases *.* and with host (computer?) name:

GRANT ALL PRIVILEGES ON *.* TO m1@"cws9.my-hosting-panel.com" IDENTIFIED BY "0";

or for all domains and IPs:

GRANT ALL PRIVILEGES ON db_yesmus.* TO 'ymm'@'%' IDENTIFIED BY "yes-mus-mus";

GRANT ALL PRIVILEGES ON db_yesmus.* TO ymm@"%.mbirgin.com" IDENTIFIED BY "yes-mus-mus";

GRANT ALL PRIVILEGES ON *.* TO m1gin@"173.0.54.91" IDENTIFIED BY "159753" ;

Thats it!

GRANT ifadesi operation.ascx ile de kullanılabiliyor!

GRANT ile kullanıcıyı sınırlamak da mümkün görünüyor!

GRANT USAGE ON *.* TO 'jeffrey'@'localhost' WITH MAX_QUERIES_PER_HOUR 90;

Kullanıcı silmek için mysql.user tablosundan ilgili kayıtlar direkt olarak silinebilmektedir.

SELECT Host, User FROM mysql.user;

DELETE FROM mysql.user WHERE User=”m1” AND Host=”localhost”

veya

DROP USER 'jeffrey'@'localhost';

GRANT ifadesinde komut kısıtlaması da yapılabilmekte:

GRANT SELECT, INSERT, UPDATE, DELETE, DROP, ALTER ON tmp.* TO user@localhost;

http://dev.mysql.com/doc/refman/5.5/en/grant.html

http://www.ghacks.net/2009/12/27/allow-remote-connections-to-your-mysql-server/




  • Tabloya Veri Ekleme İpuçları:

Tabloya veri eklenirken otomatik artan ID alanına dahi atama yapılabilmektedir!

INSERT INTO db_common_mb.tblurl (id, url, title, counter, daterecord, flag)

VALUES (122, "http://www.mbirgin.com/test.mp3", "tırnaklı ""özel\" başlık", 0, now(), 0);

Tabloya bir sorguda çoklu veri eklenebilmektedir.

INSERT INTO db_common_mb.tblurl (url, title, daterecord)

VALUES

("url1", "title 1", now()),

("url2", "title 2", now()),

("url3", "title 3", now());



  • SORGUDA SIRA NUMARASI OLUŞTURMAK

SET @Row=0;

SELECT @Row:=@Row+1 AS No, title, id

FROM tblyt

Not: @Row gibi parametre tanımlayabilmek için, connection string ifadesine

Allow User Variables=True eklenmelidir.





DATABASE BACKUP

Veritabanı yedekleme:

mysqldump -u m1 -h proje.mbirgin.com -p mb_mysql_1 > backup.sql

Windows Sunucuda Yedekleme:
C:\Program Files\MySQL\MySQL Server 5.6\bin>mysqldump -h localhost -u b4o -pPass db_blog4yon > c:\inetpub\wwwroot\mbirgin\mbirgin.com\sil\b4o_2013-10-16.sql

Parolayı da aynı satırda girme:

mysqldump -u m1 -h proje.mbirgin.com -ptest123 mb_mysql_1 > backup.sql

Birden çok veritabanını yedekleme:

mysqldump -u root -p123 --databases db_blog4yon db_common > 2db.sql

Bir kullanıcıya ait tüm veritabanlarını yedekleme:

  • mysqldump --user=root --all-databases -p > root_all.sql

Bir kullanıcıya ait tüm veritabanlarını doğrudan başka bir sunucuya gönderme:

  • mysqldump --user=root --all-databases -p | ssh targethost 'mysql --user=user2'

veya

  • mysql -u root -p < root_all.sql


Veritanını yedekleme ve sıkıştırma:

  • mysqldump -u blog4yon -h proje.mbirgin.com -p db_blog4yon | gzip -9 > b4o.sql.gz
  • mysqldump -u blog4yon -h 75.126.153.165 -ppass db_blog4yon | gzip -9 > b4o_2013-02-22.sql.gz;
  • mysqldump -u m1gin -h proje.mbirgin.com -p db_common_mb tblyt | gzip -9 > db_common-tblyt.sql.gz
  • mysqldump -u m1gin -h 75.126.153.165 -ppass db_common_mb | gzip -9 > db_common_2013-02-22.sql.gz;
  • mysqldump -u root -p123 --databases db_blog4yon db_common | gzip > 2db.sql.gz


Veritabanındaki bir tabloyu yedekleme:

  • mysqldump -u m1 -h proje.mbirgin.com -p mb_mysql_1 tbltest > backup_tbltest.sql
  • mysqldump -u m1 -h proje.mbirgin.com -ptest123 mb_mysql_1 tbltest > bck_tbltest.sql
  • mysqldump -u m1 -h proje.mbirgin.com -p mb_mysql_1 tbltest | gzip > backup_tbltest.sql.gz

Belli sayıda kayıt yedekleme:

  • mysqldump -u m1 -h proje.mbirgin.com -ptest123 --databases mb_mysql_1 --tables tbltest --where="1 limit 100" > backup_tbltest_part.sql

Kritere göre kayıt yedekleme

  • mysqldump -u m1 -h proje.mbirgin.com -ptest123 --databases mb_mysql_1 --tables tbltest --where="id<11" > backup_tbltest_filter.sql
  • mysqldump -u m1 -h proje.mbirgin.com -ptest123 --databases mb_mysql_1 --tables tbltest --where="id<111 limit 10" > bckp_filterlimit.sql


mysql Komutu ile MySql Server işlemleri

mysql komutu ile yerel yahut uzaktaki sunucuya bağlanılarak işlemler yapılabilir.

mysql -u root -p;

mysql>select * from tbl1;

mysql -u m1 -h proje.mbirgin.com -ptest123 mb_mysql_1;

mysql>show tables;

mysql -u m1 -h proje.mbirgin.com -ptest123 mb_mysql_1 -e "select * from tbltest limit 11";

Çıktıyı yerel dosyaya kaydetmek:

mysql -u m1 -h proje.mbirgin.com -ptest123 mb_mysql_1 -e "select * from tbltest limit 11" > dump_1.txt;



DATABASE RESTORE

# mysql -u root -p[root_password] [database_name] < dumpfilename.sql

Veritabanını yerel dosyadan güncellemek...

mysql db_test < /tmp/backup.sql;

mysql -h 199.85.212.52 -u blog4yon -ppass db_blog4yon < /var/www/html/b4o.sql;

mysql -u m1gin -h 199.85.212.52 -ppass db_common_mb < db_common_2013-02-22.sql ;

mysql --default-character-set=utf8 -u m1gin -h 199.85.212.52 -ppass db_common_mb < db_common_2013-02-22.sql ;

Kriterlere göre bir başka veritabanından güncelleme yapmak:

mysqldump -u m1 -h proje.mbirgin.com -ptest123 --databases mb_mysql_1 --tables tbltest --where="id<111 limit 10" | mysql db_test;

proje.mbirgin.com ‘daki veritabanından elde edilen kayıtlarla yerel veritabanı güncellenmektedir.
Hedef veritabanı var/oluşturulmuş olmalıdır.

Dikkat! Hedef tablo varsa içerisindeki tüm verilerle silinip yeniden oluşturulmaktadır!
Bunun önüne geçmek için, --no-create-info=true parametresi kullanılabilir.

Ancak bu durumda primary key ile çakışan kayıtlarda hata oluşmaktadır.

Kaynak ve hedef veritabanı farklı bilgisayarlarda ise, hedefte boş bir yeni veritabanı oluşturulup ilgili tablolar oraya aktarıldıktan sonra, UPDATE komutuyla kriterlere uygun güncelleme yapılabilir.
İşlem tamamlandığında oluşturulan geçici veritabanı/tablo silinebilir.


Örnek Uygulama:

proje.mbirgin.com ‘daki veritabanında bulunan bir kaydı, var olan yedekten güncellemek.

mysql -u m1 -h proje.mbirgin.com -ppass test </var/www/html/db_b4o_2012-12-22.sql ;

mysql -u blog4yon -h proje.mbirgin.com -ppass ;

mysql> show databases;

mysql> use test;

mysql> show tables;

mysql> select * from tblcontent where id=207;

mysql> UPDATE db_blog4yon.tblcontent, test.tblcontent

SET db_blog4yon.tblcontent.html=test.tblcontent.html, db_blog4yon.tblcontent.title=test.tblcontent.title

WHERE db_blog4yon.tblcontent.id=test.tblcontent.id AND db_blog4yon.tblcontent.id IN (207, 31, 32, 33);

mysql> drop table test.tblcontent;


Reset mysql server root Password:

http://dev.mysql.com/doc/refman/5.0/en/resetting-permissions.html

http://planetghost.com/mysql/recover-mysql-root-password-if-forgotten


MySql Errors and Solutions:

Error:

ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2)

or

ERROR 2003 (HY000): Can't connect to MySQL server on '127.0.0.1' (111)

Solution:

nano /etc/mysql/my.cnf

Ubuntu 16.04: nano /etc/mysql/mysql.conf.d/mysqld.cnf




Remove/comment below line and restart the service.

bind-address = 127.0.0.1



Errors:

unable to set mysql root password

mysql status: mysql start/post-start ----- spawn

Unknown/unsupported storage engine: InnoDB

Çözüm için çok uğraşıldı. Sorunun sebebi anlaşılmadı. Ancak /var/log/mysql/error.log dosyasındaki hata mesajından hareketle, /etc/mysql/my.cnf dosyasına şu ifade eklenerek sorun çözüldü.

# * InnoDB

innodb_use_native_aio = 0

Etkisi varmıdır bilmiyorum ama öncesinde mysql-server-5.5 tümden silinip yeniden yüklendi.

sudo apt-get remove --purge mysql-server mysql-client mysql-common

sudo apt-get autoremove

sudo apt-get autoclean

sudo deluser mysql

sudo rm -rf /var/lib/mysql # Dikkat! Veritabanı dosyaları!!! Yedek alınmış olsun!

sudo rm -rf /etc/mysql*

sudo apt-get purge mysql-server-core-5.5

sudo apt-get purge mysql-client-core-5.5

Ardından şu şekilde kurulum yapıldı.

apt-get install mysql-server

Kurulum sırasında şifre ataması başarısız oldu. Çözüm olarak, başta belirtilen innodb_use_native_aio = 0 ataması yapıldıktan sonra yeni bir şifre atandı.

mysqladmin -u root password new_password

Ardından /var/lib/mysql konumundan alınan veritabanı yedekleri yerlerine kopyalandı ve sahiplikleri devredildi.

unzip mysql_raw_backup.zip -d /var/lib/mysql/

chown -R mysql:mysql /var/lib/mysql

Ve böylece mysql sorunsuz başladı.

(mbirgin - 2014-06-01)


System.IndexOutOfRangeException
Could not find specified column in results: id

web.config içerisinden culture="tr-TR" iken bu hata fırlıyordu. culture="en-GB" veya culture="auto" olarak değiştirildiğinde sorun çözüldü.

culture="auto" seçeneği sisteme bağlı olarak (tr-TR ise) hata fırlatabilir gibi duruyor.

(mbirgin - 2019-08)


Set Some Defaults Settings

  • nano /etc/mysql/my.cnf
  • Ubuntu 16.04: nano /etc/mysql/mysql.conf.d/mysqld.cnf

[mysqld]

character-set-server=utf8

collation-server=utf8_general_ci

lower_case_table_names = 1

# Set tablenames as case insensitive.


ERROR:

mysqldump: Error: 'The user specified as a definer ('mysql.infoschema'@'localhost') does not exist' when trying to dump tablespaces
mysqldump: Couldn't execute 'SHOW DATABASES': The user specified as a definer ('mysql.infoschema'@'localhost') does not exist (1449)

SOLUTION:

  1. sudo systemctl stop mysql
  2. sudo mysqld --upgrade=FORCE
  3. sudo systemctl start mysql

https://stackoverflow.com/questions/62127983/error-1449-hy000-the-user-specified-as-a-definer-mysql-infoschemalocalho

Add to:
Contact - About - Help