Databases
Info: Configure Redmine on cpanel hosting account with sending and receiving emails.
Wiki : http://en.wikipedia.org/wiki/Redmine
Redmine is a free and open source, web-based project management and bug-tracking tool. It includes a calendar and Gantt charts to aid visual representation of projects and their deadlines. It handles multiple projects. Redmine provides integrated project management features, issue tracking, and support for various version control systems.
The design of Redmine is significantly influenced by Trac, a software package with some similar features.
Redmine is written using the Ruby on Rails framework. It is cross-platform and cross-database. It is part of the Bitnami app library that provides an installer and virtual machine for ease of deployment.
Before starting installation you have to make sure that Ruby on rails is working fine in your environment, If not you can follow the installation document for more help.
Installaing Ruby on Rails with Cpanel : https://enlook.wordpress.com/2013/11/19/howto-install-ruby-on-rails-with-cpanel/
Once you have done, then start the redmine installation steps.
Login to the terminal using primary account logins.
#ssh myaccount@mydomain.com
- Create rails_app folder and redmine folder within it then go inside that folder
# mkdir -p ~/rails_apps/redmine/ # cd ~/rails_apps/redmine/
- Download redmine redmine-2.3.3 or latest stable version, extract it and move the content out of it, then delete the files not being used.
-
# wget http://files.rubyforge.vm.bytemark.co.uk/redmine/redmine-2.3.3.tar.gz # tar -zxvf redmine-2.3.3.tar.gz # mv redmine-2.3.3/* ./ # rm -rf redmine-2.3.3/
-
- Move example files where they can be used
# cd config # mv database.yml.example database.yml # mv configuration.yml.example configuration.yml
- Creating the MySQL Database/User/Password
Login to Cpanel account, Create a database , user and grant full privilege to the new user for the particular database.
- Modifying your database.yml file.
# vi database.yml production: adapter: mysql database: redmine host: localhost username: myaccount_databaseuser password: newpassowd encoding: utf8
- Updating the ~/rails_apps/redmine/public/.htaccess file
# cd ../public/ # pwd
- You should see something similar to this.
/home/myaccountuser/rails_apps/redmine/public
- Add these lines
Options -MultiViews PassengerResolveSymlinksInDocumentRoot on #Set this to whatever environment you'll be running in RailsEnv production RackBaseURI / SetEnv GEM_HOME /home/myaccountuser/rails_apps/redmine/public # set to resolve avoid rails control to the folder for image resolution RewriteEngine On RewriteCond %{REQUEST_URI} ^/images.* RewriteRule .* - [L]
- Create a subdomain eg: projects.mydomain.com
Follow cpanel procedure to create subdomain. - Remove projects folder inside public_html and create symbolic link.
# rm -rf ~/public_html/projects
- Creating the symlink
# ln -s ~/rails_app/redmine/public ~/public_html/projects
- Updating Environment variables in ~/.bashrc file
- Add these lines to the bottom of your ~/.bashrc file
export HPATH=$HOME export GEM_HOME=$HPATH/ruby/gems export GEM_PATH=$GEM_HOME:/lib64/ruby/gems/1.9.3 export GEM_CACHE=$GEM_HOME/cache export PATH=$PATH:$HPATH/ruby/gems/bin export PATH=$PATH:$HPATH/ruby/gems
- after which source your .bashrc file
# source ~/.bashrc
- You will then need to check your rails version
rails -v && rake --version && gem -v
- You should get this message
Rails 4.0.1 rake, version 0.9.2.2 1.8.23
- Running bundle install
# cd ~/rails_apps/redmine/ # bundle install # rake generate_session_store
- Running generate_session_store or generate_secret_token
-
# rake generate_session_store
- If you get an error saying that command is deprecated, run this command instead;
# rake generate_secret_token
-
- Start the site session
# rake db:migrate RAILS_ENV=production
- Configuring outgoing emailsUpdate the setting in configuration.yml
default: email_delivery: delivery_method: :smtp smtp_settings: address: localhost port: 25 domain: mydomain.com authentication: :none enable_starttls_auto: false
Now the redmine have capable to send emails using exim install in the cpanel server.
- Configuring Incomming emails for IMAPCreate a cron job for the script to get continuous email feaching
For the first this script must execute from the terminal, so it will display error if any.
/usr/bin/rake -f /home1/innovat4/rails_apps/redmine/Rakefile --silent redmine:email:receive_imap RAILS_ENV="production" port=143 host=mydomain.com username=projects@mydomain.com password=myemailpassword
For more help follow the official link http://www.redmine.org/projects/redmine/wiki/RedmineReceivingEmails#Enabling-unknown-users-to-create-issues-by-email
Note : Each configuration required rails environment reboot for that you can follow the simple way.
# touch ~/rails_app/redmine/tmp/reboot.txt
ERROR: SQL error: [unixODBC][Driver Manager]Data source name not found, and no default driver specified
One of our server getting odbc_connection error after migration, this will usually happens because ODBC module has not enabled on the server, While I tried to set up ODBC with freeTDS in order to connect to a MSSQL server faced several issues.
MSSQL uses Tabular Data Stream (TDS) as a communication protocol which is same like in Sybase. freeTDS is an implementation of TDS protocol.
Before trying to connect with freetds to the mssql server, make sure, that your MSSQL server has remote access to connect.
[02-May-2013 02:21:31 America/Denver] PHP Warning: odbc_connect() [<a href='function.odbc-connect'>function.odbc-connect</a>]: SQL error: [unixODBC][Driver Manager]Data source name not found, and no default driver specified, SQL state IM002 in SQLConnect in /home/anand/public_html/mssql2000.php on line 69
We need below packages installed to get this done.
1, php-odbc
2, unixODBC
3, freetds
Installation :
#pecl install php-odbc #yum install unixODBC-devel unixODBC freetds-devel freetds
Or,
Download unixODBC and untar it
#wget ftp://ftp.unixodbc.org/pub/unixODBC/unixODBC-2.3.0.tar.gz #tar –xzf unixODBC-2.3.0.tar.gz #cd unixODBC-2.3.0 ; ./configure ; make ; make install
Installing freeTDS
Set environmental variable at /etc/profile. Add following lines at end
#vi /etc/profile # TDS SYBASE=/usr/local LD_LIBRARY_PATH=${LD_LIBRARY_PATH}:$SYBASE/lib export SYBASE LD_LIBRARY_PATH #http://ibiblio.org/pub/Linux/ALPHA/freetds/stable/freetds-stable.tgz #tar –xzf freetds*.tar.gz ; cd freetds* ; ./configure --with-tdsver=8.0 --with-unixodbc=/usr/local ; make ; make install
Configuration:
Note: tdsver=8.0 if you use SQL 2000, tdsver=7.0 if you use SQL 7.0, More info about freetds.conf here
root@server [~]# cat /etc/freetds.conf [MSSQLSERVER] host = 11.222.333.44 port = 1433 tds version = 8.0
Setup ODBC:
Create template for both driver and configuration. before creating make sure that the driver files are valid.
root@server [~]# vi tds.driver.template #Driver for MS SQL [FreeTDS] #"FreeTDS" is the unique name for this driver Description = FreeTDS driver Driver = /usr/lib64/libtdsodbc.so.0 Setup = /usr/lib64/libtdsS.so.1 FileUsage = 1 UsageCount =1 root@server [~]# vi tds.datasource.template [MSSQL] #"MSSQL" is the DSN name that we call to connect database Driver = FreeTDS # name that we specified in the driver file Description = MSSQL ReflectiveLearning Trace = No TraceFile = /var/log/freetds.log Servername = MSSQLSERVER # this name specified in the freetds.conf Port = 1433 Database = reflective_Live TDS_Version = 8.0
Now install the data source and driver
The below commands will create configuration file for odbc but odbc.ini has create in users home dir by default we have to move it manually to /etc/
odbcinst -i -d -f tds.driver.template odbcinst -i -s -f tds.datasource.template cp -rpf ~/.odbc.ini /etc/odbc.ini mv /etc/odbc.ini /usr/local/etc mv /etc/odbcinst.ini /usr/local/etc cd /etc ln -s /usr/local/etc/odbc.ini ln -s /usr/local/etc/odbcinst.ini chmod 0664 /usr/local/etc/odbc*.ini cd ~
check by using command line
#tsql -H Hostname_OR_IP -p PortNumber -U Username
root@server [~]# isql -v DSNname Username 'Password' +---------------------------------------+ | Connected! | | | | sql-statement | | help [tablename] | | quit | | | +---------------------------------------+ SQL> quit
That’s all, now the application will works fine.
some PHP code to connect to it
<?php //************************************************************************* //Open Database Connection //************************************************************************* $dbserver="ipaddress"; $dbusername="tester4"; $dbpassword="password1234"; $defaultdb="testdb"; $cn = mssql_connect($dbserver,$dbusername,$dbpassword) or die("Connection Error"); $db = mssql_select_db($defaultdb,$cn) or die("Database Error"); echo "Connection Success" ?>
Some of the useful commands,
odbcinst -j # to check the ODBC version and configuration.
odbcinst -q -d # View loaded drivers.
odbcinst -q -s # View DSN entry
tsql -LH 22.33.55.44 #To list MSSQL server instant name,version etc.,
osql -S DSN -U Username -P ‘Password’ #list the configuration files loaded to connect ODBC
Install / Enable PHP MsSQL Extension in cPanel/WHM Server
For enabling mssql support in PHP on cpanel/WHM installed server we can’t use easyapache in this case, we need FreeTDS installed and configured which is a set of Unix/Linux libraries that implement the TDS protocol. First we will need to download and install FreeTDS, you can find more information and download link at http://www.freetds.org URL. Note the exact installation steps below :
yum install unixODBC unixODBC-devel
Step :2 freeTDS
cd /usr/local/src wget http://ibiblio.org/pub/Linux/ALPHA/freetds/stable/freetds-stable.tgz tar zfvx freetds-stable.tgzcd freetds-*; ./configure --prefix=/usr/local/freetds --with-tdsver=8.0 --enable-msdblib --enable-dbmfix --with-gnu-ld; make ; make install echo "--with-mssql=/usr/local/freetds" >> /var/cpanel/easy/apache/rawopts/all_php5 touch /usr/local/freetds/lib/libtds.a touch /usr/local/freetds/include/tds.h
Use this line if you get any error like no such file or directory
ln -s /usr/include/sqlext.h /usr/local/include/sqlext.h
If it it is a 64 bit server then make the following softlink
ln -s /usr/local/freetds/lib/ /usr/local/freetds/lib64
Step :3 mssql.so
Now run Easy Apache and make sure that Mysql, Mysql of the system, amd Mysqli are all selected.
/scripts/easyapache
Now you can check Mysql enabled
root@server [~]# php --info |grep "mssql" MSSQL Support => enabled
Error: unable to start mysql 130207 5:33:16 /usr/sbin/mysqld: Can’t open file: ‘sends.MYI’. (errno: 145)
MySQL crashes frequently, Getting error while starting the service “No mysqld pid file found. Looked for /var/lib/mysql/server.910mall.jp.pid.” . It is because of table crash. repair crashed tables using below commands.
Engine : MyISM
# repair the tables root@server [/var/lib/mysql]# myisamchk -r */*.MYI - recovering (with sort) MyISAM-table 'DB_wrdp1/wp_post2cat.MYI' Data records: 1 - Fixing index 1 - Fixing index 2 --------- - recovering (with keycache) MyISAM-table 'DB_wrdp1/wp_postmeta.MYI' Data records: 0 --------- - recovering (with sort) MyISAM-table 'DB_wrdp1/wp_posts.MYI' Data records: 2 - Fixing index 1 - Fixing index 2 ---------- recovering (with sort) MyISAM-table 'DB_wrdp1/wp_usermeta.MYI' Data records: 2 - Fixing index 1 - Fixing index 2 - Fixing index 3 --------- # Start Mysql root@server [/var/lib/mysql]# /etc/init.d/mysql start
Error: MySQL Innodb “Failed to locate MySQL socket. Please check the mysql configuration.”
I recently came across a Cpanel server which mysqld refused to start, Getting error “Failed to locate MySQL socket. Please check the mysql configuration“. In mysql error log default /var/lib/mysql/HOSTNAME.err it was no surprise to find that the mysql user table had been marked as crashed.
[root@server mysql]# tail -f /var/lib/mysql/HOSTNAME.err 110108 10:37:45 InnoDB: Starting log scan based on checkpoint at InnoDB: log sequence number 1 3749263016. InnoDB: Doing recovery: scanned up to log sequence number 1 3749263050 InnoDB: Last MySQL binlog file position 0 79, file name ./host89-bin.000005 110108 10:37:45 InnoDB: Flushing modified pages from the buffer pool... 110108 10:37:45 InnoDB: Started; log sequence number 1 1234563550 InnoDB: !!! innodb_force_recovery is set to 1 !!! 110108 10:37:45 [ERROR] Fatal error: Can't open and lock privilege tables: Table 'user' is marked as crashed and should be repaired 110108 10:37:45 mysqld ended
Now set mysql recovery option as 1 and start MySQL without privilege safe mode
[mysqld] innodb_force_recovery = 1
[root@server ~]#/usr/sbin/mysqld --skip-grant-tables --basedir=/ --datadir=/var/lib/mysql --user=mysql --pid-file=/var/lib/mysql/server.pid --skip-external-locking --port=3306 --socket=/var/lib/mysql/mysql.sock
Now I can login to the database, its time to repare user tables by following below steps,
[root@server ~]# mysql Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 183 to server version: 4.1.22-standard-log Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql> use mysql; mysql> check table user; +------------+-------+----------+----------------------------------------------------------+ | Table | Op | Msg_type | Msg_text | +------------+-------+----------+----------------------------------------------------------+ | mysql.user | check | warning | Table is marked as crashed | | mysql.user | check | warning | 6 clients are using or haven't closed the table properly | | mysql.user | check | error | Record at pos: 28492 is not remove-marked | | mysql.user | check | error | record delete-link-chain corrupted | | mysql.user | check | error | Corrupt | +------------+-------+----------+----------------------------------------------------------+ 5 rows in set (0.02 sec) mysql> repair table user; +------------+--------+----------+------------------------------------------+ | Table | Op | Msg_type | Msg_text | +------------+--------+----------+------------------------------------------+ | mysql.user | repair | warning | Number of rows changed from 1384 to 1385 | | mysql.user | repair | status | OK | +------------+--------+----------+------------------------------------------+ 2 rows in set (0.48 sec) mysql> check table user; +------------+-------+----------+----------+ | Table | Op | Msg_type | Msg_text | +------------+-------+----------+----------+ | mysql.user | check | status | OK | +------------+-------+----------+----------+ 1 row in set (0.01 sec) mysql>exit
Now remove the innodb recovery option and start MySQL as normal mode.
[root@server mysql]# service mysql restart
Error: Unable to connect DynamoDB in AWS Eclipse Toolkit
I am using Eclipse to connect AWS Dynamo Db and manage TABLES : by installing aws tool kit. everything will works fine but Dynamo db shows unable to connect, unfortunately here is the solution for this.If you can’t get anything else to work, you can screw with the plugin’s metadata to force an http:// connection and avoid ssl entirely.
Follow the below setps to change the metadata to http://
In your eclipse workspace, open the .metadata directory. Browse to .metadata/.plugins/com.amazonaws.eclipse.core/regions. In there is a file called regions.xml. Edit it, changing the DynamoDB entry from https://... to http://... Then restart eclipse, and you will be connecting to DynamoDB over http:// .
Steps to install AWS tool kit.
1. Install AWS Eclipse tools in a clean Eclipse environment (from Help->Eclipse Marketplace only), with no prior installations of the tools on the test machine. Simply wiping a prior installation might leave working settings, config items behind.
2. Note the DynamoDB (as of this writing) is not included in Help->Eclipse Marketplace distribution.
3. Add in DynamoDB from Help->Install New Software
4. Note that it doesn’t connect after entering credentials.
Thats it..:)
Error: #2002 – The server is not responding (or local MySQL server’s socket is not correctly configured).
While accessing PhpMyAdmin getting the following error.
ERROR
#2002 – The server is not responding (or local MySQL server’s socket is not correctly configured).
Reason :-
The mysql socket file is missing from the /tmp directory
Fix :-
1. Create a symbolic link from the original mysql socket file to /tmp
[root@test ~]# ln -s /var/lib/mysql/mysql.sock /tmp
OR
2. Restart MySQL service from WHM
WHM Login >> Main >> Restart Services >>SQL Server (MySQL)
OR
3. Edit the PhpMyAdmin configuration file to use the original MySQL socket file.
vi /usr/local/cpanel/base/3rdparty/phpMyAdmin/config.inc.php
Make sure, the correct mysql socket file is mentioned there.
~~~~~~~~~~~~~~~~
$cfg[‘Servers’][$i][‘socket’] = ‘/var/lib/mysql/mysql.sock’;
$cfg[‘Servers’][$i][‘connect_type’] = ‘socket’;
~~~~~~~~~~~~~~~~
OR
4.Change the server addr local host to physical IP .
Short List of MySQL Commands
Conventions used here:
* MySQL key words are shown in CAPS
* User-specified names are in small letters
* Optional items are enclosed in square brackets [ ]
* Items in parentheses must appear in the command, along with the parentheses
* Items that can be repeated as often as desired are indicated by an ellipsis …
Quoting in MySQL statments
* Don’t quote database, table, or column names
* Don’t quote column types or modifiers
* Don’t quote numerical values
* Quote (single or double) non-numeric values
* Quote file names and passwords
* User names are NOT quoted in GRANT or REVOKE statements, but they are quoted in other statements.
General Commands
USE database_name
Change to this database. You need to change to some database when you first connect to MySQL.
SHOW DATABASES
Lists all MySQL databases on the system.
SHOW TABLES [FROM database_name]
Lists all tables from the current database or from the database given in the command.
DESCRIBE table_name
SHOW FIELDS FROM table_name
SHOW COLUMNS FROM table_name
These commands all give a list of all columns (fields) from the given table, along with column type and other info.
SHOW INDEX FROM table_name
Lists all indexes from this tables.
SET PASSWORD=PASSWORD(‘new_password’)
Allows the user to set his/her own password.
Table Commands
CREATE TABLE table_name (create_clause1, create_clause2, …)
Creates a table with columns as indicated in the create clauses.
create_clause
column name followed by column type, followed optionally by modifiers. For example, “gene_id INT AUTO_INCREMENT PRIMARY KEY” (without the quotes) creates a column of type integer with the modifiers described below.
create_clause modifiers
* AUTO_INCREMENT : each data record is assigned the next sequential number when it is given a NULL value.
* PRIMARY KEY : Items in this column have unique names, and the table is indexed automatically based on this column. One column must be the PRIMARY KEY, and only one column may be the PRIMARY KEY. This column should also be NOT NULL.
* NOT NULL : No NULL values are allowed in this column: a NULL generates an error message as the data is inserted into the table.
* DEFAULT value : If a NULL value is used in the data for this column, the default value is entered instead.
DROP TABLE table_name
Removes the table from the database. Permanently! So be careful with this command!
ALTER TABLE table_name ADD (create_clause1, create_clause2, …)
Adds the listed columns to the table.
ALTER TABLE table_name DROP column_name
Drops the listed columns from the table.
ALTER TABLE table_name MODIFY create_clause
Changes the type or modifiers to a column. Using MODIFY means that the column keeps the same name even though its type is altered. MySQL attempts to convert the data to match the new type: this can cause problems.
ALTER TABLE table_name CHANGE column_name create_clause
Changes the name and type or modifiers of a column. Using CHANGE (instead of MODIFY) implies that the column is getting a new name.
ALTER TABLE table_name ADD INDEX [index_name] (column_name1, column_name2, …)
CREATE INDEX index_name ON table_name (column_name1, column_name2, …)
Adds an index to this table, based on the listed columns. Note that the order of the columns is important, because additional indexes are created from all subsets of the listed columns reading from left to write. The index name is optional if you use ALTER TABLE, but it is necesary if you use CREATE INDEX. Rarely is the name of an index useful (in my experience).
Data Commands
INSERT [INTO] table_name VALUES (value1, value2, …)
Insert a complete row of data, giving a value (or NULL) for every column in the proper order.
INSERT [INTO] table_name (column_name1, column_name2, …) VALUES (value1, value2, …)
INSERT [INTO] table_name SET column_name1=value1, column_name2=value2, …
Insert data into the listed columns only. Alternate forms, with the SET form showing column assignments more explicitly.
INSERT [INTO] table_name (column_name1, column_name2, …) SELECT list_of_fields_from_another_table FROM other_table_name WHERE where_clause
Inserts the data resulting from a SELECT statement into the listed columns. Be sure the number of items taken from the old table match the number of columns they are put into!
DELETE FROM table_name WHERE where_clause
Delete rows that meet the conditions of the where_clause. If the WHERE statement is omitted, the table is emptied, although its structure remains intact.
UPDATE table_name SET column_name1=value1, column_name2=value2, … [WHERE where_clause]
Alters the data within a column based on the conditions in the where_clause.
LOAD DATA LOCAL INFILE ‘path to external file’ INTO TABLE table_name
Loads data from the listed file into the table. The default assumption is that fields in the file are separated by tabs, and each data record is separated from the others by a newline. It also assumes that nothing is quoted: quote marks are considered to be part of the data. Also, it assumes that the number of data fields matches the number of table columns. Columns that are AUTO_INCREMENT should have NULL as their value in the file.
LOAD DATA LOCAL INFILE ‘path to external file’ [FIELDS TERMINATED BY ‘termination_character’] [FIELDS ENCLOSED BY ‘quoting character’] [LINES TERMINATED BY ‘line termination character’] FROM table_name
Loads data from the listed file into the table, using the field termination character listed (default is tab \t), and/or the listed quoting character (default is nothing), and/or the listed line termination chacracter (default is a newline \n).
SELECT column_name1, column_name2, … INTO OUTFILE ‘path to external file’ [FIELDS TERMINATED BY ‘termination_character’] [FIELDS ENCLOSED BY ‘quoting character’] [LINES TERMINATED BY ‘line termination character’] FROM table_name [WHERE where_clause]
Allows you to move data from a table into an external file. The field and line termination clauses are the same as for LOAD above. Several tricky features:
1. Note the positions of the table_name and where_clause, after the external file is given.
2. You must use a complete path, not just a file name. Otherwise MySQL attempts to write to the directory where the database is stored, where you don’t have permission to write.
3. The user who is writing the file is ‘mysql’, not you! This means that user ‘mysql’ needs permission to write to the directory you specify. The best way to do that is to creat a new directory under your home directory, then change the directory’s permission to 777, then write to it. For example: mkdir mysql_output, chmod 777 mysql_output.
Privilege Commands
Most of the commands below require MySQL root access
GRANT USAGE ON *.* TO user_name@localhost [IDENTIFIED BY ‘password’]
Creates a new user on MySQL, with no rights to do anything. The IDENTIFED BY clause creates or changes the MySQL password, which is not necessarily the same as the user’s system password. The @localhost after the user name allows usage on the local system, which is usually what we do; leaving this off allows the user to access the database from another system. User name NOT in quotes.
GRANT SELECT ON *.* TO user_name@localhost
In general, unless data is supposed to be kept private, all users should be able to view it. A debatable point, and most databases will only grant SELECT privileges on particular databases. There is no way to grant privileges on all databses EXCEPT specifically enumerated ones.
GRANT ALL ON database_name.* TO user_name@localhost
Grants permissions on all tables for a specific database (database_name.*) to a user. Permissions are for: ALTER, CREATE, DELETE, DROP, INDEX, INSERT, SELECT, UPDATE.
FLUSH PRIVILEGES
Needed to get updated privileges to work immediately. You need RELOAD privileges to get this to work.
SET PASSWORD=PASSWORD(‘new_password’)
Allows the user to set his/her own password.
REVOKE ALL ON [database_name.]* FROM user_name@localhost
Revokes all permissions for the user, but leaves the user in the MySQL database. This can be done for all databases using “ON *”, or for all tables within a specific databse, using “ON database_name.*”.
DELETE FROM mysql.user WHERE user=’user_name@localhost’
Removes the user from the database, which revokes all privileges. Note that the user name is in quotes here.
UPDATE mysql.user SET password=PASSWORD(‘my_password’) WHERE user=’user_name’
Sets the user’s password. The PASSWORD function encrypts it; otherwise it will be in plain text.
SELECT user, host, password, select_priv, insert_priv, shutdown_priv, grant_priv FROM mysql.user
A good view of all users and their approximate privileges. If there is a password, it will by an encrytped string; if not, this field is blank. Select is a very general privlege; insert allows table manipulation within a database; shutdown allows major system changes, and should only be usable by root; the ability to grant permissions is separate from the others.
SELECT user, host, db, select_priv, insert_priv, grant_priv FROM mysql.db
View permissions for individual databases.
MY SQL basic commands
My SQL Basic commands
# mysql -u root -p
mysql> use mysql;
mysql> INSERT INTO user (Host,User,Password) VALUES(‘%’,’username’,PASSWORD(‘password’));
mysql> flush privileges;
Change a users password from unix shell.
# [mysql dir]/bin/mysqladmin -u username -h hostname.blah.org -p password ‘new-password’
Change a users password from MySQL prompt. Login as root. Set the password. Update privs.
# mysql -u root -p
mysql> SET PASSWORD FOR ‘user’@’hostname’ = PASSWORD(‘passwordhere’);
mysql> flush privileges;
Recover a MySQL root password. Stop the MySQL server process. Start again with no grant tables. Login to MySQL as root. Set new password. Exit MySQL and restart MySQL server.
# /etc/init.d/mysql stop
# mysqld_safe –skip-grant-tables &
# mysql -u root
mysql> use mysql;
mysql> update user set password=PASSWORD(“newrootpassword”) where User=’root’;
mysql> flush privileges;
mysql> quit
# /etc/init.d/mysql stop
# /etc/init.d/mysql start
Set a root password if there is on root password.
# mysqladmin -u root password newpassword
Update a root password.
# mysqladmin -u root -p oldpassword newpassword
Allow the user “bob” to connect to the server from localhost using the password “passwd”. Login as root. Switch to the MySQL db. Give privs. Update privs.
# mysql -u root -p
mysql> use mysql;
mysql> grant usage on *.* to bob@localhost identified by ‘passwd’;
mysql> flush privileges;
Give user privilages for a db. Login as root. Switch to the MySQL db. Grant privs. Update privs.
# mysql -u root -p
mysql> use mysql;
mysql> INSERT INTO user (Host,Db,User,Select_priv,Insert_priv,Update_priv,Delete_priv,Create_priv,Drop_priv) VALUES (‘%’,’databasename’,’username’,’Y’,’Y’,’Y’,’Y’,’Y’,’N’);
mysql> flush privileges;
or
mysql> grant all privileges on databasename.* to username@localhost;
mysql> flush privileges;
To update info already in a table.
mysql> UPDATE [table name] SET Select_priv = ‘Y’,Insert_priv = ‘Y’,Update_priv = ‘Y’ where [field name] = ‘user’;
Delete a row(s) from a table.
mysql> DELETE from [table name] where [field name] = ‘whatever’;
Update database permissions/privilages.
mysql> flush privileges;
Delete a column.
mysql> alter table [table name] drop column [column name];
Add a new column to db.
mysql> alter table [table name] add column [new column name] varchar (20);
Change column name.
mysql> alter table [table name] change [old column name] [new column name] varchar (50);
Make a unique column so you get no dupes.
mysql> alter table [table name] add unique ([column name]);
Make a column bigger.
mysql> alter table [table name] modify [column name] VARCHAR(3);
Delete unique from table.
mysql> alter table [table name] drop index [colmn name];
Load a CSV file into a table.
mysql> LOAD DATA INFILE ‘/tmp/filename.csv’ replace INTO TABLE [table name] FIELDS TERMINATED BY ‘,’ LINES TERMINATED BY ‘\n’ (field1,field2,field3);
Dump all databases for backup. Backup file is sql commands to recreate all db’s.
# [mysql dir]/bin/mysqldump -u root -ppassword –opt >/tmp/alldatabases.sql
Dump one database for backup.
# [mysql dir]/bin/mysqldump -u username -ppassword –databases databasename >/tmp/databasename.sql
Dump a table from a database.
# [mysql dir]/bin/mysqldump -c -u username -ppassword databasename tablename > /tmp/databasename.tablename.sql
Restore database (or database table) from backup.
# [mysql dir]/bin/mysql -u username -ppassword databasename < /tmp/databasename.sql
Create Table Example 1.
mysql> CREATE TABLE [table name] (firstname VARCHAR(20), middleinitial VARCHAR(3), lastname VARCHAR(35),suffix VARCHAR(3),officeid VARCHAR(10),userid VARCHAR(15),username VARCHAR(8),email VARCHAR(35),phone VARCHAR(25), groups VARCHAR(15),datestamp DATE,timestamp time,pgpemail VARCHAR(255));
Create Table Example 2.
mysql> create table [table name] (personid int(50) not null auto_increment primary key,firstname varchar(35),middlename varchar(50),lastnamevarchar(50) default ‘bato’);
How to Copy backup a MySQL Database
Posted on Updated on
The mysqldump Command
The mysqldump command creates a text version of the database. Specifically, it creates a list of SQL statements which can be used to restore/recreate the original database.
# mysqldump -uusername -ppasswd dbname > backupfile.sql
or
# mysqldump -B openfire > /home/db.sql
or
# mysqldump –all-databases -r dbdump.sql -uroot-p
[username] Your database username
[passwd] The password for your database
[dbname] The name of your database
[backupfile.sql] The filename for your database backup
You can dump a table, a database, or all databases.
To dump all MySQL databases on the system, use the –all-databases shortcut:
# mysqldump -u root -p –all-databases > backupfile.sql
Restoring a MySQL Database
Use this method to rebuild a database from scratch
# mysql -u username -p passwd database_to_restore < /path/to/file.sql
Use this method to import into an existing database (i.e. to restore a database that already exists)
# mysqlimport -u sadmin -p pass21 db_name DBback.sql
or
# mysqlimport options database textfile
or
mysql>use dbname; <select the existing DB >
mysql>source /home/db.sql; <restore backup to the db>
Share this:
This entry was posted in Databases, Mysql Comments and tagged Database.