Problems and Solutions during mysql Installation

1. Install mysql1 in windows. Set the system environment variable and create a new system variable named MYSQL_HOME in the system environment variable. The value of the variable is: mysql installation Path. Add one Path to the Path: % MYSQL_HOME % \ bi

1. Install mysql1 in windows. Set the system environment variable and create a new system variable named MYSQL_HOME in the system environment variable. The value of the variable is: mysql installation Path. Add one Path to the Path: % MYSQL_HOME % \ bi

1. Install mysql in windows

1. Set system environment variables

Create System variables in system environment variables

Variable name: MYSQL_HOME

Variable value: mysql installation path

Add one Path to Path: % MYSQL_HOME % \ bin

2. Change the my-default.ini TO my. ini.

3. Modify the my. ini file

Basedir =\\ mysql installation path

Path of the bin directory in the installation file of datadir =\\ mysql

Port = 3306

4. Start the mysql Service

Enter command line mode

Net start MySQL startup Service

Net stop MySQL close Service

5. Modify the root password:

C:> mysql-u root enters mysql

Mysql> show databases;

Mysql> use mysql;

Mysql> update user set password = password ("password") where user = 'root ';

Mysql> flush privileges;

Mysql> quit

2. Connect to MySQL

Format: mysql-h host address-u user name-p User Password

1. Example 1: connect to MYSQL on the local machine.

Mysql-u root-p. After you press enter, the system prompts you to enter the password. If you have just installed MYSQL, the Super User root has no password, so press enter to enter MYSQL, the MYSQL prompt is: mysql>.

2. Example 2: connect to MYSQL on the remote host. Assume that the IP address of the remote host is 110.110.110.110, the user name is root, and the password is abcd123. Enter the following command:

Mysql-h110.110.110.110-uroot-pabcd123

(Note: you do not need to add spaces for u and root. The same applies to others)

3. exit MYSQL command: exit (Press ENTER ).

3. Change the password

Format: mysqladmin-u username-p old password New password

1. Example 1: Add a password ab12 to the root user. First, enter the directory mysqlbin in DOS, and then type the following command:

Mysqladmin-uroot-password ab12

Note: because the root account does not have a password at the beginning, the old-p password can be omitted.

2. Example 2: Change the root password to djg345.

Mysqladmin-uroot-pab12 password djg345

4. Add new users. (Note: Unlike the above, the following commands are in the MySQL environment and are in the Hong Kong server, so a semicolon is followed as the command Terminator)

Format: grant select on database. * to username @ login host identified by \ "password \"

Example 1: Add a user named "test1" with the password "abc" to the website space so that the user can log on to any host and have the permission to query, insert, modify, and delete all databases. First, use the root user to connect to MySQL, and then type the following command:

Grant select, insert, update,

Delete on *. * to test2 @ localhost identified by \ "abc \";

If you do not want test2 to have a password, you can run another command to remove the password.

Grant select, insert, update, delete on mydb

. * To test2 @ localhost identified \"\";

The above describes logon, user addition, password change, and other issues. Next, let's take a look at the database operations in MySQL. Note: you must first log on to MySQL. The following operations are performed at the MySQL prompt and each command ends with a semicolon.

5. Solutions to Character Set errors:

① The following problems occur:

Mysql> update users

-> Set username = 'guan Yu'

-> Where userid = 2;

ERROR 1366 (HY000): Incorrect string value: '\ xB9 \ xD8 \ xD3 \ xF0' for column 'usern

Ame' at row 1

② An error occurred while displaying the characters inserted in the table:

Mysql> select * from users;

+ -------- + ---------- +

| Userid | username |

+ -------- + ---------- +

| 2 | ???? |

| 3 | ???? |

| 4 |? Í? Optional |

+ -------- + ---------- +

3 rows in set (0.00 sec)

Chinese characters in the table are garbled.

Use the command status

Mysql> status

--------------

Mysql Ver 14.12 Distrib 5.0.45, for redhat-linux-gnu (i686) using readline 5.0 Connection id: 30

Current database:

Current user: root @ localhost

SSL: Not in use

Current pager: stdout Using outfile :''

Using delimiter :;

Server version: 5.0.45 Source distribution

Protocol version: 10

Connection: Localhost via UNIX socket

Server characterset: latin1

Db characterset: latin1

Client characterset: latin1 Conn. characterset: latin1

UNIX socket:/var/lib/mysql. sock

Uptime: 33 days 58 min 53 sec

Threads: 1 Questions: 72 Slow queries: 0 Opens: 25 Flush tables: 1 Open tables: 18 Queries per second avg: 0.000

--------------

Check mysql and find that the character set of Server characterset and Db characterset is set to latin1, so Chinese garbled characters are displayed.

Mysql> show tables;

+ ---------------- +

| Tables_in_test |

+ ---------------- +

| Users |

+ ---------------- +

1 row in set (0.00 sec)

Modify the character set of a table.

Mysql> alter table users character set GBK;

Query OK, 3 rows affected (0.08 sec)

Records: 3 Duplicates: 0 Warnings: 0

View the table structure:

Mysql> show create table users;

+ ------- + -----------------------------------------------------------------------

------------------------------------------------------------------------------ +

| Table | Create Table

|

+ ------- + -----------------------------------------------------------------------

------------------------------------------------------------------------------ +

| Users | create table 'users '(

'Userid' int (11) default NULL,

'Username' char (20) character set latin1 default NULL

) ENGINE = InnoDB default charset = gbk |

+ ------- + -----------------------------------------------------------------------

------------------------------------------------------------------------------ +

1 row in set (0.00 sec)

Mysql> desc users;

+ ---------- + ------ + ----- + --------- + ------- +

| Field | Type | Null | Key | Default | Extra |

+ ---------- + ------ + ----- + --------- + ------- +

| Userid | int (11) | YES | NULL |

| Username | char (20) | YES | NULL |

+ ---------- + ------ + ----- + --------- + ------- +

2 rows in set (0.02 sec)

Insert Chinese characters into the table and an error occurs.

Mysql> insert into users values (88, 'Chinese ');

ERROR 1366 (HY000): Incorrect string value: '\ xD6 \ xD0 \ xCE \ xC4' for column 'usern

Ame' at row 1

You also need to change the username Character Set of the users table.

Mysql> alter table users modify username char (20) character set gbk;

ERROR 1366 (HY000): Incorrect string value: '\ xC0 \ xEE \ xCB \ xC4' for column 'usern

Ame' at row 1

Because data already exists in the table, changing the username character set is not ***

Clear Data in the users table

Mysql> truncate table users;

Query OK, 3 rows affected (0.01 sec)

Change the username character set in the user table

Mysql> alter table users modify username char (20) character set gbk;

Query OK, 0 rows affected (0.06 sec)

Records: 0 Duplicates: 0 Warnings: 0

Then, insert a Chinese character ***.

Mysql> insert into users values (88, 'Chinese ');

Query OK, 1 row affected (0.01 sec)

Mysql> select * from users;

+ -------- + ---------- +

| Userid | username |

+ -------- + ---------- +

| 88 | Chinese |

+ -------- + ---------- +

1 row in set (0.00 sec)

Mysql>

6. C Programming for mysql in linux:

To specify the path of the database file mysql. h, generally

/Usr/include/mysql. h

The libmysqlclient. so path must be specified during compilation:

/Usr/lib/mysql/libmysqlclient. so

Command: gcc point C file name/usr/lib/mysql/libmysqlclient. so

This article is from the "7098269" blog. Be sure to keep this source

, Website Space