Problems and Solutions during mysql Installation

1. Install mysql in windows 1. Set the system environment variable to create a new system variable named MYSQL_HOME in the system environment variable. The value of the variable "MYSQL_HOME" is: Add a Path to the mysql installation Path in the Path: % MYSQL_HOME % \ bin2. Change my-default.ini to my. ini3, modify my. INI file basedir =\\ mysql installation path datadir =\\ mysql Installation File bin directory path port = 33064, start mysql Service Enter command line mode input net start MySQL start service net stop MySQL to close the Service 5. Change the root password: c:> mysql-u root enter mysql> show databases; mysql> use mysql; mysql> update user set password = password ("password") where user = 'r Oot '; mysql> flush privileges; mysql> quit 2. Connection 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. Run the following command: mysql-h110.110.110.110-uroot-pabcd123. Note: you do not need to add spaces to the u and root nodes.) 3. Run the exit MYSQL command: 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 enter the following command: mysqladmin-uroot-password ab12 Note: because the root has no 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 in the MySQL environment are followed by a semicolon as the command Terminator.) Format: grant select on database. * to username @ login host identified by \ "password \" Example 1. Add a user test1 whose password is abc so that he can log on to any host, all databases are permitted to query, insert, modify, and delete 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, run another command to remove the password. Grant select, insert, update, delete on mydb. * to test2 @ localhost identified by \ "\"; the logon, adding users, and changing passwords are discussed above. 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. Character set ERROR solution: ①. The problem is as follows: 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 ②. the following error occurs when the characters inserted in the table are displayed: mysql> select * from users; + -------- + ---------- + | userid | username | + -------- + ---------- + | 2 | ???? | 3 | ???? | 4 |? Í? Characters | + -------- + ---------- + 3 rows in set (0.00 sec) table contains Chinese Characters in garbled characters. Run statusmysql> 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 characters Et: 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 characters are garbled. Mysql> show tables; + ---------------- + | Tables_in_test | + ------------------ + | users | + ---------------- + 1 row in set (0.00 sec) change the character set of the 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; + ------- + tables ------------------ + | Table | Create Table | + ------- + --------------------------------------------------------- Users + | users | create table 'users' ('userid' int (11) default NULL, 'username' char (20) character set latin1 default NULL) ENGINE = InnoDB default charset = gbk | + ------- + bytes -------------------------------------------------------------------------------------------------------------------------------------------------- --- + 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) When a Chinese character is inserted into the table, an error occurs. Mysql> insert into users values (88, 'Chinese'); ERROR 1366 (HY000): Incorrect string value: '\ xD6 \ xD0 \ xCE \ xC4' for column 'username' at row 1 also needs 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 'username' at row 1 because the table already has data, therefore, the operation to change the username character set does not ** clear the data in the users table mysql> truncate table users; Query OK, 3 rows affected (0.01 sec) changing 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) Reco Rds: 0 Duplicates: 0 Warnings: 0 Then insert Chinese characters and insert them ***. 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: Specify the database file mysql. h path, usually/usr/include/mysql. h indicates libmysqlclient during compilation. so path:/usr/lib/mysql/libmysqlclient. so command: gcc point C file name/usr/lib/mysql/libmysqlclient. so

This article is from the "7098269" blog, please be sure to keep this source http://7108269.blog.51cto.com/7098269/1203714