In this post I will try to do a quick tour of the various functions of MySql that a developer may need. This will include:
- starting and stopping a MySQL server
- creation / destruction of a MySql database
- import / export a MySQL database
- viewing the contents of a MySQL database command mode
- hibernate configuration to access a MySQL database
These mechanisms have been tested on a database server under 5.0 or 5.1 on Windows XP 7 64 bit.
MySql for Windows 7 64 bit
Before I begin, I want to indicate the existence of a 64-bit version of MySQL for Windows 7. Msi is named "mysql-1.5.1943-winx64.msi.
It installs correctly in the directory "C: \\ Program Files \\ MySQL \\ MySQL Server 5.1" which is the location for 64-bit process.
I installed it as I start service manually.
This latest version no longer seems to accept a blank password for root.
Starting and Stopping MySQL Server Startup and
the decision depends on your MySQL server installation entant that service or not.
MySql is not a service
To start is to stop if not installed and MySql as a service, I created two BAT files, but the lines they contain can be typed directly from the command line:
To start the server:
"C: \\ Program Files \\ MySQL \\ MySQL Server 5.0 \\ bin \\ mysqld" - console
To stop the server:
"C: \\ Program Files \\ MySQL \\ MySQL Server 5.0 \\ bin \\ mysqladmin " -U root shutdown
Beware of double quotes (") used to take into account the space and must imperatively be completed at the end of the command (before its parameters).
Check out the directory of your MySQL installation, it has an impact on the command given above.
MySql is a service
I use the service interface of Windows: Windows 7, I "pinned the program in the task bar. To do this, simply right-click its icon once it has been launched and choose "Pin this program in the task bar. To restart it, just click on it once.
To start Windows 7, if it has not made a shortcut, I type Services in the input box and I select the Services programs (not services.exe).
It is also possible to go through the Control Panel, then "Administrative Tools" and services.
Once the window "Service" open, just select the services (local) MySQL in the list and press the Start link to start or Stop to stop it. Create
or destroy a MySQL database
By default there is a basic test. You may need to create a particular base for a demo or a particular development if you are working on several projects.
With this mechanism the data of different databases are isolated. You can safely not only add data to a database without impacting the other, but you can even create tables with the same name and different structures in two different bases.
To begin, it is imperative that the MySQL server is running (See how to start the MySQL server in the previous chapter).
Then we'll launch a command console on MySQL. To do this, open a DOS console, for example by typing cmd.exe.
At the prompt, you move the directory containing the MySQL binary:
cd C: \\ Program Files \\ MySQL \\ MySQL Server 5.0 \\ bin
Then type: mysql-
uroot-pmonmotdepasse
Where root is the user and the password mypassword this root.
To create the database, type:
create database dbname;
Where dbname is the name you want for your database (Do not test that is already used).
To use the database, you must do next:
use dbname;
thereby put themselves in the database concerned.
To destroy a base, just type:
drop database dbname;
Warning! no confirmation message is displayed. You lose everything: data and database structure.
Export / Import a MySQL database
To do these operations, it is necessary that the starting point of the export works as well as for imports of arrival. To start a MySQL database to see the previous chapter.
Export a MySQL database
The operation is performed from the command line of MySQL. We must start a DOS console using cmd.exe. Then we must perform the following steps:
- your view on the MySQL bin directory
- Making the dump.
cd C: \\ Program Files \\ MySQL \\ MySQL Server 5.0 \\ bin mysqldump dbname-
uroot-pmonmotdepasse> filename.log
Where:
- dbname is the name of the MySQL database
- root is the name of "user (in a development context, we often use the default administrator)
- mypassword is the password for the root user
- filename.log is the name of the export file. It will be created in the directory where you are. In this case "C: \\ Program Files \\ MySQL \\ MySQL Server 5.0 \\ bin".
file was created in my case in ANSI format. My base was in ANSI format.
Importing a MySQL database
The operation is performed From the command line of MySQL. The file was exported with the mechanism described above. We must start a DOS console using cmd.exe. Then we must perform the following steps:
- your view on the MySQL bin directory
- Connect to MySQL
- Create the MySQL database in which the import will be made
- Positioning based
- import (source control)
cd C: \\ Program Files \\ MySQL \\ MySQL Server 5.1 \\ bin mysql-
uroot-pmonmotdepasse
create database dbname;
use dbname;
source c: / filename.log
Where:
- dbname is the name of the MySQL database
- root is the name of "user (in a context development, we often use the default administrator)
- mypassword is the password for the root user
- filename.log is the name of the export file. He had been placed in the directory C: \\. Note that I use the syntax C: / instead of C: \\ because the backslash is an escape character for MySQL.
In my case the file was created in ANSI format. My destination was based on it as UTF-8. By simply importing, accented characters are not displayed in the right format. To solve the problem, I used PSPad to pass the format of my file in UTF-8. We just have the Format menu, select UTF-8 instead of ANSI. Consult
data from a MySQL database
Above all, the MySQL server must be started (see why the second chapter of this post).
The operation is performed from the command line mode of MySQL.Il must start a DOS console using cmd.exe. Then we must perform the following steps:
- your view on the MySQL bin directory
- Connect to MySQL
- Positioning based
- Enter SQL commands
cd C: \\ Program Files \\ MySQL \\ MySQL Server 5.1 \\ bin mysql-
uroot pmonmotdepasse
-use test;
Where:
- test is the name of the MySQL database
- root is the name of "user (in a development context, we often use the default administrator)
- mypassword is the password for the root user
SQL commands are the most conventional:
- show tables;: list database tables
- desc tablename ;: gives a description of the structure of the table tablename
- select * from tablename WHERE ...;: list data from a table. Attention to specify a WHERE clause if you do not want to go through all the data from a table
- select count (*) from tablename WHERE ...;: counts the number of records in the table that satisfies the WHERE clause. It may be wise to use this command before using the previous one.
- show variables like '% character_set'; allows to know the charset of the database.
To exit, just type: exit
Configuring hibernate. Cfg.xml for MySQL
To use hibernate, you must define a hibernate.cfg.xml (usually placed under src) which contains the information necessary for Hibernate to connect to the MySQL database.
\u0026lt;session-factory>
\u0026lt;property name="hibernate.connection.driver_class"> com.mysql.jdbc.Driver \u0026lt;/ property> ;
\u0026lt;property name="hibernate.connection.url"> jdbc: mysql: / / localhost / test \u0026lt;/ property>
; \u0026lt;property name="hibernate.connection.username"> root \u0026lt;/ property>
\u0026lt;property name="hibernate.connection.password"> mypassword \u0026lt;/ property> \u0026lt;property
name="hibernate.dialect"> org.hibernate. dialect.MySQL5InnoDBDialect \u0026lt;/ property>
\u0026lt;/ session-factory>
Where:
- test is the name of the MySQL database
- root is the name the "user (in a development context, we often use the administrator default)
- mypassword is the password for the root user
We also need a MySQL jar. This jar MySQL "mysql-connector-java-5.0.8-bin.jar" may take place in two locations:
- directly in the lib directory of your Tomcat installation: For example: "C: \\ Program Files \\ Apache Software Foundation \\ Tomcat 6.0 \\ lib ". In this case, the MySQL jar will be available to all Web applications on that server is Tomcat
- in directory WebContent / WEB-INF / lib of your project. In this case, it will repeat as many times as needed (for each project)
The second solution makes sense only in the case of demo or examples that do not fit into a normal operating: one seeks autonomy and ease without the need for traceability. That is why we will integrate the MySQL driver in the deployment package.
philosophy JEE is the first solution: Install bookstores JEE server. The database is something serious enough that the holding is concerned with the subject and must establish a traceability used versions. Conclusion
This post aims to give a developer the minimum necessary to feel comfortable in the handling of MySQL. The information in this post should be in no way used as a guideline for the installation and deployment of a MySQL database in production. To complete
aspects developments, you can read my post about data types and Hibernate JPA which shows the relationships between types MySQL and JPA annotations.
0 comments:
Post a Comment