Oct 02 2016

Unknown variable default character set utf8 MySQL 5.5 or MariaDB 5.5

Category: Errors and SolutionsFatih Acar @ 08:52

If you get an error like “Unknown variable default character set utf8” MySQL or MariaDB 5.5 in /var/log/mariadb/mariadb.log when I restart MySQL database, you can resolve this problem with change variable name.

Error

You can look for error /var/log/mariadb/mariadb.log
/usr/libexec/mysqld: unknown variable ‘default-character-set=utf8’
Aborting

mysql
Solution
Continue reading “Unknown variable default character set utf8 MySQL 5.5 or MariaDB 5.5”

1,473 total views, 8 views today

Tags: MySQL Administration, MySQL Error Solutions


Jul 01 2016

MySQL Frequently Using Commands

Category: AdministrationFatih Acar @ 08:42

MySQL is one of the most using database system. Almost all web sites are using MySQL database. I wrote most using commands of MySQL database.I will share various commands using examples for MySQL database .

mysql

Connect MySQL

[root@testdb ~]# mysql
[root@testdb ~]# mysql -h hostip
[root@testdb ~]# mysql -h hostip -u username
[root@testdb ~]# mysql dbname -u username
[root@testdb ~]# mysql dbname -u username -P portnumber

List, Add, Drop MySQL Database

List databases

mysql> show databases;

Add database

mysql> create database testdb

Drop database

mysql> drop database testdb

List, Add, Drop, Change and Grant User

mysql> use mysql
mysql> create user facar;
mysql> drop user facar;
mysql> update user set password=PASSWORD(“newpassword”) where user=’facar’;
mysql> grant all privileges on testdb.* to facar;
mysql> grant all privileges on *.* to facar;
mysql> revoke all privileges on testdb.* from facar;
mysql> revoke all privileges on *.* from facar;
mysql> flush privileges;
mysql> grant usage on testdb.* to facar identified by ‘password’;
mysql> grant usage on *.* to facar identified by ‘password’;
mysql> revoke usage on testdb.* from facar
mysql> revoke usage on *.* from facar
mysql> flush privileges;

Information Queries of MySQL Database

mysql> help;

mysql> use testdb;
mysql> show tables;
mysql> desc tablename;

mysql> connect mysql;
mysql> select user();

mysql> show variables;
mysql> show variables where variable_name = ‘Port’;

[root@testdb ~]# mysqladmin –help
[root@testdb ~]# mysqladmin –version
[root@testdb ~]# mysqladmin ping
[root@testdb ~]# mysqladmin variables

768 total views, 2 views today

Tags: MySQL, MySQL Administration


Sep 15 2013

MySQL Database Import And Export Operations

Category: Administration,Backup And RecoveryFatih Acar @ 20:43

Take Backup With Data

Open Command Prompt (cmd)
# mysqldump -u dbusername -p db_name > /tmp/db_fullbackup.sql

Take Only Metadata Backup

# mysqldump -u dbusername -p –no-data db_name > /tmp/db_backupmetadata.sql
or
# mysqldump -u dbusername -p -d db_name > /tmp/db_backupmetadata.sql

Restore Database Metadata From Backup

# mysql -u dbusername -p db_name < /tmp/db_backupmetadata.sql

Restore Database From Backup

# mysql -u dbusername -p db_name < /tmp/db_fullbackup.sql

7,102 total views, no views today

Tags: Backup and Recovery, MySQL, MySQL Administration


Aug 22 2013

MySQL Data File Size Decrease

Category: Administration,MySQLFatih Acar @ 16:06

If ibdata1 data file size is too big, you can decrease this file. My scenario is that my database have a table and size is 100 GB, I want to take backup and delete all data with decrease datafile size. If I use truncate command, this is not decrease ibdata1 size. I can use backup and drop scenario. I am using MySQL 5.x on Windows Server.

1-Take Backup With Data

Open Command Prompt (cmd)
> mysqldump.exe -u root -p db_name > E:\db_backup.sql

2-Take Metadata Backup

Open Command Prompt
> mysqldump.exe -u root -p –no-data db_name > E:\db_backupmetadata.sql
or
> mysqldump.exe -u root -p -d db_name > E:\db_backupmetadata.sql

3-Drop Database

Open MySQL Client Console
mysql> show databases;
mysql> drop database db_name;

4-Stop MySQL Service

Open Services.msc
MySQL servise has to do stop

5-Delete Data Files

Open Data Directory
You can delete ibdata1, ib_logfile0, ib_logfile1 … start with ib..
This files will be recreated as automatic by MySQL service when MySQL service start

6-Start MySQL Service

Open Services.msc
MySQL servise has to do start

7-Create Database

Open MySQL Client Console
mysql> create database db_name;

8-Restore Metadata From Backup

Open Command Prompt (cmd)
> mysql.exe -u root -p db_name < E:\db_backupmetadata.sql

38,820 total views, no views today

Tags: Database Administration, MySQL, MySQL Administration