Setting UTF-8 as the default encoding for MySQL

Edit the MySQL configuration file from the following path /etc/mysql/my.cnf and set the below configuration: #/etc/mysql/my.cnf [client] default-character-set = utf8   [mysql] default-character-set = utf8   [mysqld] collation-server = utf8_unicode_ci init-connect = ‘SET NAMES utf8’ character-set-server = utf8 Restart the mysql server using the following command /etc/init.d/mysql restart
Continue reading…

Enjoyed this post? Share it!

 
 

MySQL server security measures

Operating system security process can be improved with the following recommendations: Install software as ‘root’ OS user. The file permissions of all MySQL binary and support files are to be owned by ‘root’. Restrict access to the ‘root’ OS user via sudo privileges. Be diligent by only granting access in limited form. Configure an OS […]
Continue reading…

Enjoyed this post? Share it!

 
 

MySQL Security Precautions

Developers/Administrators can improve security for MySQL client access with the following recommendations: Always set a MySQL ‘root’ user password. Change the MySQL ‘root’ user id to a different name, e.g. ‘dba’. Only enable SUPER privileges to dba accounts, and only ever for ‘localhost’. Application user permissions should be as restrictive as possible. Never use ‘%’ […]
Continue reading…

Enjoyed this post? Share it!

 
 

Connect to MySQL server from command prompt

Here is a simple example to connect to MySQL server from command prompt: [root@host]# mysql -u root -p Enter password:****** This will give you mysql> command prompt where you will be able to execute any SQL command. Following is the result of above command: Welcome to the MySQL monitor. Commands end with ; or \g. […]
Continue reading…

Enjoyed this post? Share it!

 
 

Check mysql server status using mysqladmin utility

Use mysqladmin binary to check server version. This binary would be available in /usr/bin on linux and in C:\mysql\bin on windows. [root@host]# mysqladmin –version It will produce following result on Linux. It may vary depending on your installation: mysqladmin Ver 8.23 Distrib 5.0.9-0, for redhat-linux-gnu on i386 If you do not get such message then […]
Continue reading…

Enjoyed this post? Share it!

 
 

Installing MySQL on Windows

MySQL now comes neatly packaged with an installer. Simply download the installer package, unzip it anywhere, and run setup.exe. Default installer setup.exe will walk you through the trivial process and by default will install everything under C:\mysql. Test the server by firing it up from the command prompt the first time. Go to the location […]
Continue reading…

Enjoyed this post? Share it!

 
 

Load data files into tables with mysqlimport in MySql

It is possible to load a data file directly into a table in MySql. The prerequisite is to prepare the data file as one line per data row, and use tab character as the column delimiter. The data file name should match the target table name. Example: >cd \mysql\bin >type \temp\links.tab dev.w3mentor.com www.mysql.com   >mysqlimport […]
Continue reading…

Enjoyed this post? Share it!

 
 

mysqlimport and commands in MySql

mysqlimport is a command-line interface for administrators or end users to load data files into tables program tool to load data into tables. The following are the mysqlimport commands “mysqlimport databaseName fileName” – Imports the data from the specified file to the specified database. The data will be loaded into the table who’s name matches […]
Continue reading…

Enjoyed this post? Share it!

 
 

Dump a table to file with mysqldump

we can dump all rows in a table from the server to a file using mysqldump with the “-f fileName” option. The following example illustrates how to dump a table to file with mysqldump. >cd \mysql\bin >mysqldump -u root -r \temp\w3.txt test links >type \temp\w3.txt   >type \temp\w3.txt | more — MySQL dump 10.10 — […]
Continue reading…

Enjoyed this post? Share it!

 
 

mysqldump commands

mysqldump is a command-line interface for administrators or end users to export data from mysql server to files. commands supported by mysqldump: “mysqldump databaseName tableName” – Dumps the specified table in the specified database. “mysqldump databaseName” – Dumps all the tables in the specified database.
Continue reading…

Enjoyed this post? Share it!

 
 

Show table names with mysqlshow in MySql

We need to specify a database name if we want to show table names with mysqlshow. The followings tutorial exercise illustrates how to get all table names that match a pattern: >cd \mysql\bin >mysqlshow –verbose mysql time% Database: mysql Wildcard: time% +—————————+———-+ | Tables | Columns | +—————————+———-+ | time_zone | 2 | | time_zone_leap_second […]
Continue reading…

Enjoyed this post? Share it!

 
 

mysqlshow in MySql

mysqlshow is a command-line interface for end users to see information on tables and columns. The following commands are supported by mysqlshow: “mysqlshow” – Shows all the databases. “mysqlshow databaseName” – Shows all the tables in the specified database. “mysqlshow databaseName tableName” – Shows all the columns in the specified table. “mysqlshow –verbose” – Shows […]
Continue reading…

Enjoyed this post? Share it!

 
 

Using mysqlcheck in MySql

mysqlcheck is a command-line interface for administrators to check and repair tables. Here are some commands supported by “mysqlcheck”: “mysqlcheck databaseName tableName” – Checks the specified table in the specified database. “mysqlcheck databaseName” – Checks all tables in the specified database. “mysqlcheck –all-databases” – Checks all tables in all databases. “mysqlcheck –analyze databaseName tableName” – […]
Continue reading…

Enjoyed this post? Share it!

 
 

show all tables with mysql commandline interface

Running the non-SQL command “SHOW TABLES” at the “mysql” prompt will allow us to all the tables in a database.   >cd \mysql\bin >mysql -u root test Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 14 to server version: 5.0.24   mysql> SHOW TABLES; +—————-+ | Tables_in_w3mdb| […]
Continue reading…

Enjoyed this post? Share it!

 
 

Executing commands on MySql commandline interface

We need to start “mysql” and enter the SQL statement at the “mysql” prompt.   >cd \mysql\bin >mysql -u root test Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 4 to server version: 5.0.24   mysql> CREATE TABLE links (name VARCHAR(80)); Query OK, 0 rows affected (0.10 […]
Continue reading…

Enjoyed this post? Share it!

 
 

MySql commandline interface

mysql is a command-line interface for end users to manage user data objects. You can use “mysql” to run any standard SQL statements against the server. “CREATE TABLE items (name VARCHAR(80));” – Creates a table called “items” with one column. “INSERT INTO items VALUES (‘mysql.w3mentor.com’);” – Inserts a table row into “items” table. “SELECT * […]
Continue reading…

Enjoyed this post? Share it!