Start server (Linux):
% /sbin/service mysqld restart
Stop server (Linux):
% /sbin/service mysqld stop
Update /etc/hosts to have all the appropriate hosts entries (all the important hosts in your network), otherwise, you cannot connect from a remote host. Set root password to access MySQL server:
% mysqladmin -u root password mypasswdhere
Access MySQL server as root:
% mysql -u root -p
Create a database:
mysql> CREATE DATABASE myappdb;
View existing databases:
mysql> SHOW DATABASES;
View existing tables in a database called myappdb:
mysql> USE myappdb; mysql> SHOW TABLES;
Grant permissions:
mysql> GRANT ALL ON myappdb.* TO dbuser@localhost IDENTIFIED BY 'myuserpass';
If trying to access from a remote host (i.e. something other than localhost), make sure that that host's FQDN is in the /etc/hosts file. Otherwise you will get “Connection Refused.” For example:
mysql> grant all on *.* to root@192.168.0.62 identified by 'somepassword';
Delete a database called myappdb:
mysql> DROP DATABASE myappdb;
Execute a script containing SQL statements:
mysql> SOURCE myscriptname.sql;
Create a table alumni in database aucdb:
mysql> CREATE TABLE `alumni` ( -> `id` TINYINT (3) UNSIGNED DEFAULT '0' AUTO_INCREMENT, -> `name` VARCHAR (20), -> PRIMARY KEY(`id`));
Describe a table:
mysql> DESCRIBE alumni;
Delete a table:
mysql> DROP TABLE alumni;
Execute an SQL statement:
mysql> SELECT * FROM alumni;
Insert records to table:
mysql> INSERT INTO pet -> VALUES ('Puffball','Diane','hamster','f','1999-03-30',NULL);
Load data from external source:
mysql> LOAD DATA LOCAL INFILE "pet.txt" INTO TABLE pet;
Delete records:
mysql> DELETE FROM alumni WHERE id=999;
Update records:
mysql> UPDATE alumni SET name='John' WHERE id=999;
Use any of these methods:
1. Copy all the files in /var/lib/mysql/* to some backup directory (Linux). Make sure to copy all ib*
files (e.g. ibdata1
, and optionally ib_logfile0
and ib_logfile1
) if using InnoDB tables:
server1% cp /var/lib/mysql/* /data/backup/mysql server2% cp -r /data/backup/mysql /var/lib server2% chown -R mysql:mysql /var/lib/mysql server2% mysql_upgrade -u root -p --force Restart mysql server
2. Copy data using mysqldump
:
server1% mysqldump > dbase.mysql server2% mysqldump < dbase.mysql For backup database % mysqldump -u root -pPassword -P3309 --routines --no-data originaldb > "d:\dbwithnodata.sql" For restoration of database % mysql -u root -pPassword -P3309 newdb < "d:\dbwithnodata.sql" Alternatively, duplicate database in same server: % mysqldump -u user -pMyPass -d originaldb | mysql -u user -pMyPass -Dnewdb The new database must exist already. The -d flag in the ''mysqldump'' command prevents copying of data.
3. Archive and compress the data (Linux):
% tar -czvf /data/backup/mysql-data-backup.tar.gz /var/lib/mysql
4. Better yet, use phpMyAdmin to backup the database.
The procedure under Windows 1):
SET PASSWORD FOR 'root'@'localhost' = PASSWORD('MyNewPassword');
C:\> C:\mysql\bin\mysqld-nt --init-file=C:\mysql-init.txt
Run these commands:
$ service mysql stop $ mysqld_safe --skip-grant-tables & $ mysql -u root mysql> use mysql; mysql> update user set authentication_string=PASSWORD("YOUR-NEW-ROOT-PASSWORD") where User='root'; mysql> flush privileges; mysql> quit $ service mysql stop $ service mysql start $ mysql -u root -p
NOTE: Note: prior to MySQL 5.7 the column was called password
instead of authentication_string
.
$ cd /var/www/html/support $ tar -xzvf phpMyAdmin_x.x.x.tar.gz
config.inc.php
. You can use setup script provided in distribution (scripts/setup.php
) to create basics of config file. See Setup chapter for details. If you don't like setup or want to fine tune resulting configuration, open (or create in case of starting from scratch) config.inc.php in your favorite editor and fill in there values for host, user, password and authentication mode to fit your environment. Edit the following fields if simply using a default file phpmyadmin/config.inc.php
: $cfg['PmaAbsoluteUri'] = 'http://www.your-host.com/yourdirectory/'; $cfg['blowfish_secret'] = 'MySecretPhraseHere'; $cfg['Servers'][$i]['host'] = 'localhost'; // MySQL hostname or IP address $cfg['Servers'][$i]['port'] = ''; // MySQL port - leave blank for default port $cfg['Servers'][$i]['auth_type'] = 'cookie'; // Authentication method (config, http or cookie based)? $cfg['Servers'][$i]['user'] = 'root'; // MySQL user $cfg['Servers'][$i]['password'] = ''; // MySQL password (only needed with 'config' auth_type)
SET SESSION FOREIGN_KEY_CHECKS=0;