Install and configure MySQL decompressed version in Win7

I originally wanted to build the Hive remote mode and found that there are no other databases except oracle and sybase on the machine. I want to dress up with a mysql instance. I plan to install mysql on the host Win7, and then place the Hive environment in the virtual machine so that the "remote mode" can be implemented. The impression is that mysql is too simple, because it was previously installed with the installation version, directly next

I originally wanted to build the Hive remote mode and found that there are no other databases except oracle and sybase on the machine. I want to dress up with a mysql instance. I plan to install mysql on the host Win7, and then place the Hive environment in the virtual machine so that the "remote mode" can be implemented. The impression is that mysql is too simple, because it was previously installed with the installation version, directly next

I originally wanted to build the Hive remote mode and found that there are no other databases except oracle and sybase on the machine. I want to dress up with a mysql instance. I plan to install mysql on the host Win7, and then place the Hive environment in the virtual machine so that the "remote mode" can be implemented. The impression is that mysql is too simple, because it was previously installed with the installation version, and it is OK to directly next and next. If configured, there will also be a client. So I am going to have a compressed version for this time. I haven't played it for a few years. I have encountered many problems. Simply put, I would like to warn myself: forgetting the past is a betrayal...

1. installation:

:

Http://www.mysql.com/downloads/mysql/

I use the previous mysql-5.5.17-winx64.zip file. After decompression, I can:

Ii. Configuration:

1) default parameter file. There are five. ini files under the installation directory. These five are template files, which are basically the same as those under the visual test. The main difference is that the allocated memory size is different.

(1) my-small.ini is designed for small databases. This model should not be used in databases that contain common projects.
② My-medium.ini is designed for medium-sized databases. If you are using RHEL in an enterprise, there may be much more physical memory than the minimum RAM requirement of the Operating System (256 MB. It can be seen that if there is so much RAM memory available, you can naturally run other services on the same machine.
My-large.ini is designed for a computer dedicated to SQL database. Since it can use up to MB of memory for the database, at least 1 GB of RAM is required for this type of system so that it can simultaneously process the operating system and database applications.
4 my-huge.ini is designed for the database in the enterprise. Such databases require dedicated servers and 1 GB or more RAM. ⑤ My-innodb-heavy-4G.ini is used only for InnoDB installation, a maximum of 4g ram, support for large queries. Create a configuration file named my. ini to start mysql. When mysql is started. in the INI file, the configuration information in the file is read as the default parameter. INI files and services can also be started. I don't know where the specific default parameters come from (does anyone know ?), However, I am sure that the default configuration is not read from the five template files in the directory. The order in which mysql reads configuration files is:
C: \ my. ini --> C: \ my. cnf --> C: \ WINDOWS \ my. ini --> C: \ WINDOWS \ my. cnf --> installation directory \ my. ini --> installation directory \ my. cnf

You can create or copy a file and save it as my. ini by referring to the five templates. You can set specific parameters, such as ports, memory, Character Set, and magic horse.

How to bind an IP address to a Mysql Server:

Bind-address = 127.0.0.1 is added to my. ini, and then restart mysql to bind an IP address.

2) environment variables. Add the mysql installation path to the path, so that you do not need to enter the full path every time you execute the command under the bin directory.

Always configure to the bin directory under the mysql directory, F: \ InstallProgram \ work \ mysql \ mysql-5.5.17-winx64 \ bin

3). Start the command. The mysql startup command is bin \ mysqld.exe. You are too lazy to enter the command every time. You can create a batch file under the installation project, and it will be okay at all. Create a text file, enter bin \ mysqld.exe -- console and save it as start. bat. Then double-click it. Now there are two more files in the directory:

4). Database Configuration.

1. start: Double-click start. bat to start the service:

If the page is displayed, the startup is successful. If the startup fails, check the configuration file.

2. Logon:

-U root indicates to log on as the root user. If the user name is not specified, the user will log on anonymously. We recommend that you delete the anonymous user directly (otherwise, problems will occur in the future, which will be mentioned later ). We didn't need to enter the password when logging on. This is because the default password for the root user of mysql compressed version is empty. You can see the user table in the mysql database:

1. Mysql> select * from mysql. user;

Looks like the figure is too small? In other words, the following permission-related fields are truncated:

User table introduction:

A user is a table in mysql that records user and related permissions.

Primary Key: Host + User

Host: the Host from which the account is allowed to connect.

User: account.

Password: Password.

--- The following parts are directly copied from the Internet, ^ _ ^-

1. Select_priv. Determine whether the user can SELECT data using the SELECT command.
   
2. Insert_priv. Determine whether the user can INSERT data using the INSERT command.
   
3. Update_priv. Determine whether the user can use the UPDATE command to modify existing data.
   
4. Delete_priv. Determine whether the user can use the DELETE command to DELETE existing data.
   
5. Create_priv. Determine whether users can create new databases and tables.
   
6. Drop_priv. Determine whether the user can delete existing databases and tables.
   
7. Reload_priv. Determine whether the user can execute specific commands used to refresh and reload MySQL internal cache, including logs, permissions, hosts, queries, and tables.
   
8. Shutdown_priv. Determine whether the user can shut down the MySQL server. Exercise caution when giving this permission to any user other than the root account.
   
9. Process_priv. Determine whether the user can view the processes of other users through the show processlist command.
   
10. File_priv. Determine whether the user can execute the select into outfile and load data infile commands.
    
11. Grant_priv. Determine whether the user can grant other users the permissions granted to the user. For example, if a user can insert, select, and delete information in the foo database and GRANT the GRANT permission, the user can GRANT any or all of its permissions to any other user in the system.
    
12. References_priv. Currently, it is only a placeholder for some future functions.
    
13. Index_priv. Determine whether you can create or delete table indexes.
    
14. Alter_priv. Determine whether the user can rename or modify the table structure.
    
15. Show_db_priv. Determine whether the user can view the names of all databases on the server, including databases with sufficient access permissions. You can consider disabling this permission for all users, unless there is a particularly irresistible reason.
    
16. Super_priv. Determine whether the user can execute some powerful management functions, such as using the KILL command to delete user processes, using the SET
    
17. GLOBAL modifies GLOBAL MySQL variables and executes commands on replication and logs.
    
18. Create_tmp_table_priv. Determine whether the user can create a temporary table.
    
19. Lock_tables_priv. Determine whether you can use the lock tables command to block Table Access/modification.
    
20. Execute_priv. Determine whether the user can execute the stored procedure. This permission is valid only in MySQL 5.0 and later versions.
    
21. Repl_slave_priv. Determine whether the user can read binary log files used to maintain the replication database environment. This user is located in the main system, facilitating communication between the host and the client.
    
22. Repl_client_priv. Determine whether the user can determine the location of the replication slave server and master server.
    
23. Create_view_priv. Determine whether a user can create a view. This permission is valid only in MySQL 5.0 and later versions. For more information about views, see Chapter 34th.
    
24. Show_view_priv. Determine whether the user can view the view or understand how the view is executed. This permission is valid only in MySQL 5.0 and later versions. For more information about views, see Chapter 34th.
    
25. Create_routine_priv. Determine whether the user can change or discard stored procedures and functions. This permission is introduced in MySQL 5.0.
    
26. Alter_routine_priv. Determine whether users can modify or delete storage functions and functions. This permission is introduced in MySQL 5.0.
    
27. Create_user_priv. Determine whether the USER can execute the create user command to CREATE a new MySQL account.
    
28. Event_priv. Determine whether a user can create, modify, and delete events. This permission is added to MySQL 5.1.6.
    
29. Trigger_priv. Determine whether a user can create or delete a trigger. This permission is added to MySQL 5.1.6.

In addition, by default, mysql does not allow remote access or remote access by the root user, because the host Field Values localhost and 127.0.0.1 both indicate local access. The host field can also be assigned an IP address or %, indicating that the host can be connected anywhere.

3. Modify the initialization account:

1. Mysql> update mysql. user set password = PASSWORD ('root') where user = 'root' or user = '';
   
2. Query OK, 4 rows affected (0.00 sec)
   
3. Rows matched: 4 Changed: 4 Warnings: 0
   
4. 
   
5. Mysql> grant all on *. * to root @ '%' identified by 'root ';
   
6. Query OK, 0 rows affected (0.00 sec)
   
7. 
   
8. Mysql> select * from mysql. user;
   
9. 
   
10. Mysql> commit;
    
11. Query OK, 0 rows affected (0.00 sec)
    
12. 
    
13. Mysql> flush privileges;
    
14. Query OK, 0 rows affected (0.01 sec)

PS: the PASSWORD function indicates that the PASSWORD is encrypted; otherwise, the plaintext is used.

Looks like this picture is small too? In other words, the following permission-related fields are truncated:

OK. The root account and anonymous account problems are fixed temporarily.

4. configure a hive account:

1. Mysql> grant all on *. * to 'hive' @ '%' identified by 'hive' with grant option;
   
2. Query OK, 0 rows affected (0.00 sec)
   
3. 
   
4. Mysql> commit;
   
5. Query OK, 0 rows affected (0.00 sec)
   
6. 
   
7. Mysql> flush privileges;
   
8. Query OK, 0 rows affected (0.00 sec)

This is also small? In other words, the following permission-related fields are truncated:

Test the hive account:

The remote connection looks okay. What about the local connection?

Local Connection Failed. hive clearly assigned the host as % .... Why is this pinch? The culprit is the undeleted anonymous user. The reason is that the database matches the host when the user logs on, while the localhost and 127.0.0.1 in the database cannot find a user named hive, therefore, the connection fails. The solution is to delete anonymous users, but nothing else remains the same:

Log on again and you will be OK:

5. Simple test:

Visual Testing, no problem at the moment. So far, mysql has been deployed successfully.

1. Some simple operations with mysql:
   
2. First, start and stop the mysql service (for the installed version or compressed version of the registered service)
   
3. Net stop mysql
   
4. Net start mysql
   
5. Second, log on to mysql
   
6. Syntax: mysql-u user name-p User Password
   
7. Enter the mysql-uroot-p command, press enter and prompt you to enter the password, enter 12345, and then press enter to enter mysql. The mysql prompt is:
   
8. Mysql>
   
9. Note: If you are connecting to another machine, you need to add a parameter-h Machine IP address.
   
10. Third, add new users
    
11. Format: grant permission on database. * to username @ login host identified by "password"
    
12. For example, you can add a user user1 with the password password1 so that the user can log on to the machine 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:
    
13. Grant select, insert, update, delete on *. * to user1 @ localhost Identified by "password1 ";
    
14. If you want the user to log on to mysql on any machine, change localhost to "% ".
    
15. If you do not want user1 to have a password, you can run another command to remove the password.
    
16. Grant select, insert, update, delete on mydb. * to user1 @ localhost identified "";
    
17. Step 4: operate databases
    
18. Log on to mysql and run the following commands at the mysql prompt. Each Command ends with a semicolon.
    
19. 1. display the Database List.
    
20. Show databases;
    
21. By default, two databases are available: mysql and test. Mysql inventory contains the mysql system and user permission information. We change the password and add users, in fact, this database is actually operated.
    
22. 2. display the data tables in the database:
    
23. Use mysql;
    
24. Show tables;
    
25. 3. display the data table structure:
    
26. Describe table name;
    
27. 4. Create and delete databases:
    
28. Create database name;
    
29. Drop database name;
    
30. 5. Create a table:
    
31. Use Database Name;
    
32. Create table Name (Field List );
    
33. Drop table name;
    
34. 6. Clear the table records:
    
35. Delete from table name;
    
36. 7. display the records in the table:
    
37. Select * from table name;
    
38. Step 5: export and import data
    
39. 1. Export data:
    
40. Mysqldump -- opt test> mysql. test
    
41. Export the database test database to the mysql. test file, which is a text file
    
42. For example, mysqldump-u root-p123456 -- databases dbname> mysql. dbname
    
43. Export the database dbname to the mysql. dbname file.
    
44. 2. import data:
    
45. Mysqlimport-u root-p123456 <mysql. dbname.
    
46. No need to explain it.
    
47. 3. Import text data to the database:
    
48. Field data of text data is separated by the tab key.
    
49. Use test;
    
50. Load data local infile "file name" into table name;
    
51. 1: Use the SHOW statement to find out the current database on the server:
    
52. Mysql> show databases;
    
53. 2. Create a database named MYSQLDATA
    
54. Mysql> create database mysqldata;
    
55. 3: select the database you created
    
56. Mysql> use mysqldata; (when you press the Enter key to see Database changed, the operation is successful !)
    
57. 4: view the tables in the current database
    
58. Mysql> show tables;
    
59. 5. Create a database table
    
60. Mysql> create table mytable (name VARCHAR (20), sex CHAR (1 ));
    
61. 6: display the table structure:
    
62. Mysql> describe mytable;
    
63. 7. Add records to the table
    
64. Mysql> insert into MYTABLE values ("hyq", "M ");
    
65. 8: load data into database tables in text mode (for example, D:/mysql.txt)
    
66. Mysql> load data local infile "D:/mysql.txt" into table mytable;
    
67. 9: import the. SQL FILE command (for example, D:/mysql. SQL)
    
68. Mysql> use database;
    
69. Mysql> source d:/mysql. SQL;
    
70. 10: delete a table
    
71. Mysql> drop table mytable;
    
72. 11: Clear the table
    
73. Mysql> delete from MYTABLE;
    
74. 12: Update table data
    
75. Mysql> update MYTABLE set sex = "f" where name = 'hyq ';
    
76. Posted on happytian read (6) Comments (0) EDIT favorites to 365Key
    
77. 13. Back up the database
    
78. Mysqldump-u root Database Name> xxx. data
    
79. 14: Example 2: connect to MYSQL on the remote host
    
80. 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:
    
81. Mysql-h110.110.110.110-uroot-pabcd123 // remote Logon
    
82. (Note: you do not need to add spaces for u and root. The same applies to others)
    
83. 3. exit MYSQL command: exit (Press ENTER)

Http://www.dataguru.cn/forum.php? Mod = viewthread & tid = 34746.