Difference between revisions of "Mysql"
Jump to navigation
Jump to search
(Created page with "==Create mysql database in ubuntu shell== <pre> On a default settings, mysql root user do not need a password to authenticate from localhost. In this case, ou can login as roo...") |
|||
Line 29: | Line 29: | ||
mysql> | mysql> | ||
</pre> | |||
== securing MYSQL and setting a Password == | |||
<pre> | |||
/usr/bin/mysql_secure_installation | |||
</pre> | |||
==Mysql Backup and Restore == | |||
===Bakcup a single database=== | |||
<pre> | |||
done from bash - no need to log in to mysql | |||
mysqldump -u root -ppassword sugarcrm > sugarcrm.sq | |||
syntax | |||
mysqldump -u root -p[root_password] [database_name] > dumpfilename.sql | |||
</pre> | |||
===Backup a single table=== | |||
<pre> | |||
mysqldump -u root -ppassword databasename table > sugarcrm.sq | |||
</pre> | |||
===Backupup Multiple Database=== | |||
<pre> | |||
log into mysql | |||
mysql -u root -pyourpassword | |||
mysql> show databases; | |||
this will give you a list of databases | |||
For example, if you want to take backup of both sugarcrm and bugs database, execute the mysqldump as shown below: | |||
mysqldump -u root -ptmppassword --databases bugs sugarcrm > bugs_sugarcrm.sql | |||
Verify the bugs_sugarcrm.sql dumpfile contains both the database backup. | |||
# grep -i "Current database:" /tmp/bugs_sugarcrm.sql | |||
-- Current Database: `mysql` | |||
-- Current Database: `sugarcrm` | |||
</pre> | |||
=== Backup all the databases:=== | |||
<pre> | |||
The following example takes a backup of all the database of the MySQL instance. | |||
# mysqldump -u root -ptmppassword --all-databases > /tmp/all-database.sql | |||
</pre> | |||
=== Backup a specific table:=== | |||
<pre> | |||
In this example, we backup only the accounts_contacts table from sugarcrm database. | |||
mysqldump -u root -ptmppassword sugarcrm accounts_contacts > /tmp/sugarcrm_accounts_contacts.sql | |||
</pre> | |||
===Restore Database=== | |||
<pre> | |||
mysql -u username -p database < dump_file.sql | |||
</pre> | |||
== Set / Change Mysql Password == | |||
<pre> | |||
Set / change / reset the MySQL root password on Ubuntu Linux. Enter the following lines in your terminal. | |||
Stop the MySQL Server. | |||
sudo /etc/init.d/mysql stop | |||
Start the mysqld configuration. | |||
sudo mysqld --skip-grant-tables & | |||
Login to MySQL as root. | |||
mysql -u root mysql | |||
Replace YOURNEWPASSWORD with your new password! | |||
UPDATE user SET Password=PASSWORD('YOURNEWPASSWORD') WHERE User='root'; FLUSH PRIVILEGES; exit; | |||
</pre> | |||
== Reset mysql password == | |||
<pre> | |||
dpkg-reconfigure mysql-server-5.0 | |||
or | |||
dpkg-reconfigure mysql-server-5.1 | |||
Depending on which package you have installed. | |||
the will bring up the enter password screen for mysql | |||
</pre> | |||
== == | |||
<pre> | |||
</pre> | |||
== Import csv file into Php Myadmin Database == | |||
<pre> | |||
Structure has to match exactly or you will get an import error. | |||
"Invalid field count in csv input line 1" | |||
Make sure you count the fields. if there is 6 fileds (including the key) you must have 6 fields to import | |||
</pre> | |||
== == | |||
<pre> | |||
</pre> | |||
== == | |||
<pre> | |||
</pre> | </pre> |
Revision as of 16:34, 8 August 2016
Create mysql database in ubuntu shell
On a default settings, mysql root user do not need a password to authenticate from localhost. In this case, ou can login as root on your mysql server using: $ mysql -u root If a password is required, use the extra switch -p: $ mysql -u root -p Enter password: Now that you are logged in, we create a database: mysql> create database amarokdb; Query OK, 1 row affected (0.00 sec) We allow user amarokuser to connect to the server from localhost using the password amarokpasswd: mysql> grant usage on *.* to amarokuser@localhost identified by 'amarokpasswd'; Query OK, 0 rows affected (0.00 sec) And finally we grant all privileges on the amarok database to this user: mysql> grant all privileges on amarokdb.* to amarokuser@localhost ; Query OK, 0 rows affected (0.00 sec) And that's it. You can now check that you can connect to the MySQL server using this command: $ mysql -u amarokuser -p'amarokpasswd' amarokdb Your MySQL connection id is 12 Server version: 5.0.38-Ubuntu_0ubuntu1-log Ubuntu 7.04 distribution Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql>
securing MYSQL and setting a Password
/usr/bin/mysql_secure_installation
Mysql Backup and Restore
Bakcup a single database
done from bash - no need to log in to mysql mysqldump -u root -ppassword sugarcrm > sugarcrm.sq syntax mysqldump -u root -p[root_password] [database_name] > dumpfilename.sql
Backup a single table
mysqldump -u root -ppassword databasename table > sugarcrm.sq
Backupup Multiple Database
log into mysql mysql -u root -pyourpassword mysql> show databases; this will give you a list of databases For example, if you want to take backup of both sugarcrm and bugs database, execute the mysqldump as shown below: mysqldump -u root -ptmppassword --databases bugs sugarcrm > bugs_sugarcrm.sql Verify the bugs_sugarcrm.sql dumpfile contains both the database backup. # grep -i "Current database:" /tmp/bugs_sugarcrm.sql -- Current Database: `mysql` -- Current Database: `sugarcrm`
Backup all the databases:
The following example takes a backup of all the database of the MySQL instance. # mysqldump -u root -ptmppassword --all-databases > /tmp/all-database.sql
Backup a specific table:
In this example, we backup only the accounts_contacts table from sugarcrm database. mysqldump -u root -ptmppassword sugarcrm accounts_contacts > /tmp/sugarcrm_accounts_contacts.sql
Restore Database
mysql -u username -p database < dump_file.sql
Set / Change Mysql Password
Set / change / reset the MySQL root password on Ubuntu Linux. Enter the following lines in your terminal. Stop the MySQL Server. sudo /etc/init.d/mysql stop Start the mysqld configuration. sudo mysqld --skip-grant-tables & Login to MySQL as root. mysql -u root mysql Replace YOURNEWPASSWORD with your new password! UPDATE user SET Password=PASSWORD('YOURNEWPASSWORD') WHERE User='root'; FLUSH PRIVILEGES; exit;
Reset mysql password
dpkg-reconfigure mysql-server-5.0 or dpkg-reconfigure mysql-server-5.1 Depending on which package you have installed. the will bring up the enter password screen for mysql
Import csv file into Php Myadmin Database
Structure has to match exactly or you will get an import error. "Invalid field count in csv input line 1" Make sure you count the fields. if there is 6 fileds (including the key) you must have 6 fields to import