Install the mysql-noinstall version under WIndows system

Friendly reminder: This article summarizes the installation and configuration application of the mysql-noinstall version, which is the decompressed version. These operations are very commonly used. This article summarizes the installation and configuration application of the mysql-noinstall version, which is the decompressed version. These operations are very commonly used. The article does not introduce the executable file installation version of MySQL. The executable installation version has many disadvantages, so I won’t go into them one by one. In short, I like green and environmentally friendly software, including eclipse, tomcat, jboss, and apache. Even if the operating system is reinstalled, these software do not need to be reinstalled. It can be said to be done once and for all!

environment:
Windows 2000/XP/2003
mysql-noinstall-5.0.37-win32.zip

1. Download MySQL

http://www.mysql.com/downloads

2. Installation process

1. Unzip mysql-noinstall-5.0.37-win32.zip to a directory, add and unzip it to the E:myserver directory.

2. Write the mysql running configuration file my.ini
my.ini
--------------------------
[WinMySQLAdmin]
# Specify the file to start the mysql service
Server=E:\myserver\mysql-5.0.37-win32\bin\mysqld-nt.exe

[mysqld]
#Set the installation directory of mysql
basedir=E:\myserver\mysql-5.0.37-win32
#Set the storage directory for the data of the mysql database, which must be data or \xxxdata
datadir=E:\myserver\mysql-5.0.37-win32\data
#Set the character set of mysql server
default-character-set=gbk

[client]
#Set the character set of mysql client
default-character-set=gbk
--------------------------

3. To install the mysql service, enter the directory E:myservermysql-5.0.37-win32bin from the MS-DOS window and run the following command:
mysqld --install mysql5 --defaults-file= E:myservermysql-5.0.37-win32my.ini

4. Start the mysql database. In the command window above, enter the command: net start mysql5
This will start the mysql service.

5. Log in to the mysql database (locally). In the command window above, enter the command: mysql -u root -p
After pressing Enter, you will be prompted to enter your password.
The administrator root password for the initial installation of the mysql decompressed version is empty, so just press Enter again to log in to the mysql database.

If this is not the first time you log in to mysql, and you still have a user with a network address, you can use the following command to log in to the mysql server. This mysql server may be remote or local. This login method is called "remote login", and the command is as follows:
mysql -h 192.168.3.143 -u root -p
mysql -h 192.168.3.143 -u root -pleizhimin

-h specifies the login IP, -u specifies the user, and -p specifies the password. If you write nothing after -p, you will be prompted to enter the password. After -p, you can also write the password directly, so that you no longer need to enter it. Password.

6. Operate the database and tables. After logging in to the mysql database, you can perform the specified operation on the database. Use the command: use database name to specify the database object for the operation. Then you can operate the tables in the database. The operation method is of course SQL commands, haha. .

7. Change the password of mysql database administrator root
There is a mysql database by default in the mysql database. This is the database of the mysql system and is used to save database users, permissions, and many other information. To change the password, you need to operate the user table of the mysql database.

Now the root user password of mysql is still empty, which is very unsafe. Suppose you want to change the password to "leizhimin".

Still in the command window above, execute the following command:
use mysql;
grant all on *.* to root@'%' identified by 'leizhimin' with grant option;
commit;

The meaning of this command is to add a root user with all permissions and the password is "leizhimin", and this user can access not only locally but also through the network. The reason for emphasizing this is that the root user that comes with the mysql system can only be accessed locally, and the identifier after the @ character is localhost. For details, you can check the uer table of mysql data. From now on, there are two root users, one is the original system and the other is the new one. For the convenience of management, delete the root that comes with mysql and keep the newly created one. root user, the reason is that this user can access mysql through the network.

Then, the command to delete the user:
user mysql;
delete from user where user='root' and host='localhost';
commit;

In fact, the above method is an authorization command, which creates a database user while authorizing. MySQL also has a separate method for changing user passwords. Let’s see how to do it.
First, create a user lavasoft with a password of: 123456
grant all on *.* to lavasoft@'localhost' identified by '123456' with grant option;

Next, change the password of this user to: leizhimin
update user set password = password('leizhimin') where user = 'lavasoft' and host='localhost';
flush privileges;

To explain, it is best to use grant to create mysql users. Especially for mysql DBA, it is important to specify user permissions when creating users and develop good habits.

This modification method actually uses the mysql function. There are more methods, so I won’t introduce them one by one.
Another thing to note is that when changing passwords and other operations, MySQL does not allow you to specify aliases for tables, but there is no such restriction for the first time.

8. Create a database. In fact, in addition to the mysql database, there is also an empty database test for user testing.
Now continue to create a database testdb, and execute a series of sql statements to see the basic operations of the mysql database.

Create database testdb:
create database testdb;

Create a database preventively:
create database if not testdb

Create table:
use testdb;
create table table1(
username varchar(12),
password varchar(20));

Preventive creation of table aaa:
create table if not exists aaa(ss varchar(20));

View table structure:
describe table1;

Insert data into table table1:
insert into table1(username,password) values
('leizhimin','lavasoft'),
('hellokitty','hahhahah');
commit;

Query table table1:
select * from table1;

Change data:

update table1 set password='hehe' where username='hellokitty';
commit;

Delete data:
delete from table1 where username='hellokitty';
commit;

Add a column to the table:
alter table table1 add column(
sex varchar(2) comment 'gender',
age date not null comment 'age'
);
commit;

Create a table table1 from the query:
create table tmp as
select * from table1;

Delete table table1:
drop table if exists table1;
drop table if exists tmp;

9. Back up database testdb
mysqldump -h 192.168.3.143 -u root -pleizhimin -x --default-character-set=gbk >C:testdb.sql

10. Delete the database testdb
drop database testdb;

11. To restore the testdb database, first create the testdb database, and then use the following command to restore it locally:
mysql -u root -pleizhimin testdb <C:testdb.sql

12. Delete the mysql service. If you are tired of mysql and you need to uninstall it, then you only need to do this

Stop mysql service
net stop mysql5

Delete mysql service
sc delete mysql5

Then delete the msyql installation folder without leaving any traces.