Category: MySQL

All about MySQL security!

Apache, Linux, MySQL, PHP, Ubuntu Server

Install LAMP stack on Ubuntu 16.04

Update system sudo apt-get update && sudo apt-get dist-upgradesudo apt-get update && sudo apt-get dist-upgrade Install Apache sudo apt-get install apache2sudo apt-get install apache2 Install MySQL sudo apt-get install mysql-serversudo apt-get install mysql-server Install PHP sudo apt-get install php libapache2-mod-php php-mcrypt php-mysql php-mbstring php-gettextsudo apt-get install php libapache2-mod-php php-mcrypt php-mysql php-mbstring php-gettext Install PHPMyAdmin sudo …

MySQL

Copying MySQL database to another server

* Export database from old server: mysqldump -u[username] -p[password] db_name > db_backupmysqldump -u[username] -p[password] db_name > db_backup * Copy backup to new server: scp db_backup [username]@[ip_address_or_hostname]:/home/[username]/db_backupscp db_backup [username]@[ip_address_or_hostname]:/home/[username]/db_backup * Import database to new server: mysql -u [username] -p -h localhost db_name < db_backupmysql -u [username] -p -h localhost db_name < db_backup

MySQL, SQL

Create Function in MySQL

Let’s see an example: DELIMITER $$ CREATE FUNCTION `countonline`(`dtstart` DATETIME, `dtend` DATETIME) RETURNS BIGINT(20) LANGUAGE SQL NOT DETERMINISTIC READS SQL DATA SQL SECURITY INVOKER COMMENT ‘my comment’ BEGIN DECLARE cc BIGINT(20); SELECT COUNT(id) INTO cc FROM users WHERE lastseen BETWEEN dtstart AND dtend; RETURN cc; END$$   DELIMITER ;DELIMITER $$ CREATE FUNCTION `countonline`(`dtstart` DATETIME, `dtend` …

MySQL

Reset Root Password for MySQL user in Windows

1. Stop MySQL service. Run services.msc as administrator and stop it. 2. Run cmd.exe as administrator. 3. Execute: c:xamppmysqlbinmysqld.exe –skip-grant-tablesc:xamppmysqlbinmysqld.exe –skip-grant-tables 4. Run another cmd.exe as administrator. 5. Execute: c:xamppmysqlbinmysql.exec:xamppmysqlbinmysql.exe 6. Execute query: UPDATE mysql.user SET Password=PASSWORD(’newpasswd’) WHERE USER=’root’;UPDATE mysql.user SET Password=PASSWORD(‘newpasswd’) WHERE User=’root’; FLUSH PRIVILEGES;FLUSH PRIVILEGES; 7. Close cmd windows. 8. Kill mysqld process …

MySQL

Restore the MySQL root user’s full privileges in Windows

1. Stop MySQL service. Run services.msc as administrator and stop it. 2. Run cmd.exe as administrator. 3. Execute: c:xamppmysqlbinmysqld.exe –skip-grant-tablesc:xamppmysqlbinmysqld.exe –skip-grant-tables 4. Run another cmd.exe as administrator. 5. Execute: c:xamppmysqlbinmysql.exec:xamppmysqlbinmysql.exe 6. Execute query: UPDATE mysql.user SET Grant_priv=’Y’, Super_priv=’Y’ WHERE USER=’root’;UPDATE mysql.user SET Grant_priv=’Y’, Super_priv=’Y’ WHERE User=’root’; FLUSH PRIVILEGES;FLUSH PRIVILEGES; 7. Close cmd windows. 8. Kill …

MySQL

How to execute thousand of sql commands using mysql cli

1> Open windows cmd 2> Type: .mysql.exe –user=root –password -e "source C:pathtomysqlfile.sql".mysql.exe –user=root –password -e "source C:pathtomysqlfile.sql" 3> Hit Enter SQL file example: USE `databasename`;   INSERT INTO blah blah; INSERT INTO blah blah; INSERT INTO blah blah;use `databasename`; insert into blah blah; insert into blah blah; insert into blah blah;

MySQL

Create trigger to update new field value before insertion

Let’s create a new MySQL trigger to update a new field value before insertion. The new value will be selected from another table. DELIMITER $$   CREATE TRIGGER trig_new_customer BEFORE INSERT ON customers FOR EACH ROW BEGIN DECLARE var1 VARCHAR(7); SELECT customer_next_code INTO var1 FROM next_codes; SET NEW.code = var1; END $$   DELIMITER ;DELIMITER …

Databases, Frameworks, Kohana, MySQL, SQL

Kohana MySQL Database Schema

Kohana 3.3 MySQL Database Schema github CREATE TABLE IF NOT EXISTS `roles` ( `id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT, `name` VARCHAR(32) NOT NULL, `description` VARCHAR(255) NOT NULL, PRIMARY KEY (`id`), UNIQUE KEY `uniq_name` (`name`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;   INSERT INTO `roles` (`id`, `name`, `description`) VALUES(1, ‘login’, ‘Login privileges, granted after account confirmation’); INSERT INTO …