Aliyun Ubuntu 12.04 64-bit deployment Java and configuration MySQL tutorial

System version: Ubuntu 12.04 64-bit

Install (the version used for the test is mysql-server-5.5):

Apt-get Install Mysql-server

Start and stop, etc.:

/etc/init.d/mysql start

/etc/init.d/mysql restart

/etc/init.d/mysql stop

Local connection:

Mysql-uname-p

Change the MySQL encoding (for example, UTF8)

Enter MySQL to view current encoding:

Mysql> Show variables like ' character% ';

The display is as follows:

+--------------------------+----------------------------+
| variable_name | Value |
+--------------------------+----------------------------+
| character_set_client | UTF8 |
| character_set_connection | UTF8 |
| Character_set_database | Latin1 |
| Character_set_filesystem | binary |
| Character_set_results | UTF8 |
| Character_set_server | Latin1 |
| Character_set_system | UTF8 |
| Character_sets_dir | /usr/share/mysql/charsets/|
+--------------------------+----------------------------+

You can see that creating a table is a latin1 format.

Exit MySQL, modify configuration file:

/etc/mysql/my.cnf

Add to:

[Client]
Default-character-set=utf8
[Mysqld]
Character-set-server=utf8
Default-character-set=utf8//mysql version 5 before using this sentence

Need to restart MySQL after modification:

/etc/init.d/mysql Restart #启动成功

Go to MySQL again to view:

Mysql> Show variables like ' character% ';

The display is as follows:

+--------------------------+----------------------------+
| variable_name | Value |
+--------------------------+----------------------------+
| character_set_client | UTF8 |
| character_set_connection | UTF8 |
| Character_set_database | UTF8 |
| Character_set_filesystem | binary |
| Character_set_results | UTF8 |
| Character_set_server | UTF8 |
| Character_set_system | UTF8 |
| Character_sets_dir | /usr/share/mysql/charsets/|
+--------------------------+----------------------------+

The modification was successful.

* Note: You need to re-create the database and table to be effective, the data encoding in the old database will not change.

To change the MySQL database location:

The database file default storage location is/var/lib/mysql.

What is the file structure of the MySQL database, the database command created for such a child:

CREATE DATABASE MySQLdb

There will be a folder MySQLdb under the default database storage location. To manipulate a database, you must first stop the database process:

$sudo/etc/init.d/mysql Stop

This article takes the example of moving to/home/mysql, where the following command shifts the original database to a new location:

$sudo Cp–r–p/var/lib/mysql/home/mysql

Edit MySQL configuration file:

$sudo vim/etc/mysql/my.cnf

Locate the DataDir row, change the contents after the after equals number to/home/mysql, and then save the exit.

Since Ubuntu 7.10, Ubuntu has started using a security software called AppArmor, which creates an area in your filesystem that allows applications to be accessed (technical jargon: application access control). If you do not modify the AppArmor configuration file for MySQL, you will never be able to start the database service for the newly set database storage location.

Configure AppArmor:

$sudo Vim/etc/apparmor.d/usr.sbin.mysqld

Find the two lines of/var/lib/mysql/, comment out the two lines (add a symbol "#" to the two lines before or after them), and add the following before or after the two lines:

/HOME/MYSQL/R,
/home/mysql/** Rwk,

Exit after saving, execute command:

$sudo/etc/init.d/apparmor Reload

Wait for the display of reloading AppArmor Profiles:done to restart the MySQL service:

$sudo/etc/init.d/mysql Start

The MySQL database storage location is now complete.

Problems that you may encounter:

Q: When I run $sudo/etc/init.d/apparmor reload but return information

Skipping profile/etc/apparmor.d/usr.sbin.mysqld~
: Warning.

and also unable to start the MySQL service, how to do?

A: This problem may occur because you are using commands such as $sudo gedit or $gksu gedit to edit usr.sbin.mysqld this profile, and the two graphical interface text editors will generate a usr.sbin.mysqld~ in the same place file, thus affecting the AppArmor read configuration file, this problem first deletes this usr.sbin.mysqld~ file:

$sudo rm/etc/apparmor.d/usr.sbin.mysqld~

Then use $sudo vim, the terminal text editor, to edit the configuration file.

Remote link MySQL Database

To enter MySQL, execute the following command:
Format: Grant permission on database name. Table name to user @ Login host identified by "User password"; For example:
Grant Select,update,insert,delete on *.* to root@192.168.1.12 identified by "passwd";

If the value of the login host (host field) is%, it means that you can log on to the MySQL server as root on any client machine. You can modify the following command:
Update user Set host = '% ' where user = ' root ';
The grant permission is changed to all privileges to indicate that all operations are authorized, or the following commands can be used when authorizing:
Grant all privileges on *.* to root@ '% ' identified by ' passwd ';

To view the current user rights:
Use MySQL;
Select Host,user,password from user;

After the authorization is modified, the authorization cache needs to be refreshed for authorization to take effect.
FLUSH privileges;

Note: The remote link needs to modify the/etc/mysql/my.cnf file,
Find: bind-address = 127.0.0.1
Modified to: bind-address = 0.0.0.0 or comment out reboot mysql:sudo/etc/init.d/mysql restart

* When using the client link (standard (TCP/IP) link, the default schema entry needs to fill in the database name (must already exist on the server)

Import and export MySQL data

Import MySQL:

Mysql> source Name.sql (path);

Export MySQL:

Use Mysqlworkbench client-"Data export".

MySQL User management

Assume user name: Xoneday User database: Blog

1. Create a new user.

Login to MySQL

@>mysql-u root-p

@> Password

Create a user

mysql> INSERT INTO Mysql.user (Host,user,password) VALUES ("localhost", "Xoneday", Password ("1234"));

Refresh System Permission Table

Mysql>flush privileges;

This creates a user named: xoneday Password: 1234.

Then log in.

mysql>exit;

@>mysql-u xoneday-p

@> Enter password

Mysql> Login Successful

2. Authorize the user.

Login to MySQL (with root privileges). I'm logged in as root.

@>mysql-u root-p

@> Password

First create a database (blog) for the user

mysql>create Database Blog;

Authorize Xoneday users to have all the permissions of the blog database.

>grant all privileges in blog.* to xoneday@localhost identified by ' 1234 ';

Refresh System Permission Table

Mysql>flush privileges;

mysql> Other actions

/*

If you want to specify partial permissions to a user, you can write this:

Mysql>grant select,update on blog.* to xoneday@localhost identified by ' 1234 ';

Refreshes the System permission table.

Mysql>flush privileges;

*/

3. Delete the user.

@>mysql-u root-p

@> Password

Mysql>delete from user WHERE user= "xoneday" and host= "localhost";

Mysql>flush privileges;

Delete a user's database

Mysql>drop Database Blog;

4. Modify the specified user password.

@>mysql-u root-p

@> Password

Mysql>update Mysql.user Set Password=password (' New password ') where user= "Xoneday" and host= "localhost";

Mysql>flush privileges;

To view the MySQL database table size (storage space):

Suppose the table we are querying is stored in:

Database name: Zcdata
Table Name: zctable

1: First into the MySQL self-management library: INFORMATION_SCHEMA
mysql> use INFORMATION_SCHEMA;

2: Query size, display the number of bytes of data
Mysql> Select Data_length,index_length
From tables where
Table_schema= ' Zcdata '
and table_name = ' zctable ';

3: Query size, shown in MB
Mysql> Select concat (Round (sum (data_length/1024/1024), 2), ' MB ') as DATA_LENGTH_MB,
Oncat (Round (sum (index_length/1024/1024), 2), ' MB ') as INDEX_LENGTH_MB
From tables where
Table_schema= ' Zcdata '
and table_name = ' zctable ';

Common errors:

Mysqldump:got error:1045:

Resolve (assuming the root user is used):

Mysql-uroot-p
mysql> use MySQL;
mysql> Update user Set Password=password (' New password ') where user= ' root '; Query OK, 4 rows Affected (0.00 sec) Rows M Atched:4 Changed:4 warnings:0
Mysql> Flush Privileges Query OK, 0 rows Affected (0.00 sec)
Mysql> quit

Mysqldump:got error:1044:

Solve:

Add the-skip-lock-tables option. That

Mysqldump-u Dbuser-ppass db--skip-lock-tables > Db.sql

When using the Mysqlworkbench client,

Select-Advanced-Others-Cancel-lock