Expat-IT Tech Bits

Home

Contact

Links

Search this site:

Categories:

/ (287)
  Admin/ (122)
    Apache/ (10)
      HTTPS-SSL/ (4)
      PHP/ (3)
      performance/ (2)
    Cherokee/ (1)
    LAN/ (4)
    LVM/ (6)
    Monitoring/ (2)
      munin/ (2)
    SSH/ (6)
    SSL/ (1)
    Samba/ (1)
    VPN-options/ (6)
      OpenVPN/ (1)
      SSH-Proxy/ (3)
      Tinc/ (1)
      sshuttle/ (1)
    backups/ (17)
      SpiderOak/ (1)
      backuppc/ (5)
      dirvish/ (1)
      misc/ (6)
      rdiff-backup/ (1)
      rsync/ (1)
      unison/ (2)
    commandLine/ (24)
      files/ (8)
      misc/ (10)
      network/ (6)
    crontab/ (1)
    databases/ (15)
      MSSQL/ (2)
      MySQL/ (8)
      Oracle/ (3)
      PostgreSQL/ (1)
    dynamicDNS/ (2)
    email/ (11)
      Dovecot/ (1)
      deliverability/ (1)
      misc/ (1)
      postfix/ (7)
      puppet/ (1)
    iptables/ (3)
    tripwire/ (1)
    virtualization/ (9)
      VMware/ (1)
      virtualBox/ (8)
  Coding/ (14)
    bash/ (1)
    gdb/ (1)
    git/ (3)
    php/ (5)
    python/ (4)
      Django/ (2)
  Education/ (1)
  Hosting/ (27)
    Amazon/ (18)
      EBS/ (3)
      EC2/ (10)
      S3/ (1)
      commandline/ (4)
    Godaddy/ (2)
    NearlyFreeSpeech/ (3)
    Rackspace/ (1)
    vpslink/ (3)
  Linux/ (30)
    Android/ (1)
    Awesome/ (3)
    CPUfreq/ (1)
    China/ (2)
    Debian/ (8)
      APT/ (3)
      WPA/ (1)
    audio/ (1)
    encryption/ (3)
    fonts/ (1)
    misc/ (6)
    remoteDesktop/ (1)
    router-bridge/ (3)
  SW/ (45)
    Micro$soft/ (1)
    browser/ (2)
      Chrome/ (1)
      Firefox/ (1)
    business/ (28)
      Drupal/ (9)
      KnowledgeTree/ (6)
      Redmine/ (2)
      SugarCRM/ (7)
      WebERP/ (2)
      WordPress/ (1)
      eGroupware/ (1)
    chat/ (1)
    email/ (1)
    fileSharing/ (2)
      btsync/ (1)
      mldonkey/ (1)
    graphics/ (2)
    research/ (2)
    website/ (6)
      blog/ (6)
        blosxom/ (3)
        rss2email/ (1)
        webgen/ (1)
  Security/ (15)
    IMchat/ (2)
    circumvention/ (2)
    cryptoCurrency/ (1)
    e-mail/ (4)
    greatFirewall/ (1)
    hacking/ (1)
    password/ (1)
    privacy/ (2)
    skype/ (1)
  Services/ (1)
    fileSharing/ (1)
  TechWriting/ (1)
  xHW/ (14)
    Lenovo/ (1)
    Motorola_A1200/ (2)
    Thinkpad_600e/ (1)
    Thinkpad_a21m/ (3)
    Thinkpad_i1300/ (1)
    Thinkpad_x24/ (1)
    USB_audio/ (1)
    scanner/ (1)
    wirelessCards/ (2)
  xLife/ (17)
    China/ (9)
      Beijing/ (5)
        OpenSource/ (3)
    Expatriation/ (1)
    Vietnam/ (7)

Archives:

  • 2016/07
  • 2016/05
  • 2016/02
  • 2016/01
  • 2015/12
  • 2015/11
  • 2015/06
  • 2015/01
  • 2014/12
  • 2014/11
  • 2014/10
  • 2014/09
  • 2014/07
  • 2014/04
  • 2014/02
  • 2014/01
  • 2013/12
  • 2013/10
  • 2013/08
  • 2013/07
  • 2013/06
  • 2013/05
  • 2013/04
  • 2013/02
  • 2013/01
  • 2012/12
  • 2012/10
  • 2012/09
  • 2012/08
  • 2012/07
  • 2012/06
  • 2012/05
  • 2012/04
  • 2012/03
  • 2012/01
  • 2011/12
  • 2011/11
  • 2011/10
  • 2011/09
  • 2011/08
  • 2011/07
  • 2011/06
  • 2011/05
  • 2011/04
  • 2011/02
  • 2010/12
  • 2010/11
  • 2010/10
  • 2010/09
  • 2010/08
  • 2010/07
  • 2010/06
  • 2010/05
  • 2010/04
  • 2010/03
  • 2010/02
  • 2010/01
  • 2009/12
  • 2009/11
  • 2009/10
  • 2009/09
  • 2009/08
  • 2009/07
  • 2009/06
  • 2009/05
  • 2009/04
  • 2009/03
  • 2009/02
  • 2009/01
  • 2008/12
  • 2008/11
  • 2008/10
  • 2008/09
  • Subscribe XML RSS Feed

    Creative Commons License
    This work is licensed under a Creative Commons Attribution-ShareAlike 4.0 International License.
    PyBlosxom

    This site has no ads. To help with hosting, crypto donations are accepted:
    Bitcoin: 1JErV8ga9UY7wE8Bbf1KYsA5bkdh8n1Bxc
    Zcash: zcLYqtXYFEWHFtEfM6wg5eCV8frxWtZYkT8WyxvevzNC6SBgmqPS3tkg6nBarmzRzWYAurgs4ThkpkD5QgiSwxqoB7xrCxs

    Thu, 04 Feb 2016


    /Admin/databases/MySQL: Master-Slave Replication: First Steps

    MASTER CONFIGURATION:

    There are a lot of search engine hits for this subject, I liked Rackspace's contribution[1] the best personally. I am going to improvise off of that document. And I am going to assume there is an existing MySQL server that will be the master that we will replicate. First of all, the new master will need a 'slave' user:

    grant replication slave on *.* TO slave_user@'ip-address' identified by 'password';
    flush privileges;

    And on a Debian / Ubuntu server, make these changes to /etc/mysql/my.cnf:

    # bind-address = 127.0.0.1
    server-id = 1
    log_bin = /var/log/mysql/mysql-bin.log
    expire_logs_days = 15
    max_binlog_size = 200M
    binlog_ignore_db = mysql

    Then restart MySQL. We comment out bind-address to permit non-localhost connections to the MySQL master from the slave. Both master and slave need a defined server-id, and they need to be different. log_bin is where the master records transactions that the slave will later pickup. The rest should be self-explanatory, except to say that in my setup binlog_ignore_db seems to be ignored. I wish it was not, but so far no major consequences.

    COPY DATABASES:

    Before replication can be started, the databases on both ends need to be exactly the same. On the master:

    FLUSH TABLES WITH READ LOCK;
    SHOW MASTER STATUS;

    The first line puts all master databases into read-only mode, and the second line will print out the file name and position (an integer) at which the binlog's record of writes to the database stopped. It is very important to record these two values, as they will be needed later on the slave. Now dump all the databases except (optionally) mysql and (not optionally) information_schema and performance_schema (the latter two are internal MySQL things that do not replicate). First get a list of all databases:

    mysql -uroot -p -e 'show databases' --skip-column-names | tr '\n' ' '

    Edit the above list to remove mysql, information_schema and performance_schema, and then dump all databases:

    mysqldump -uroot -p --databases list-of-databases | gzip > alldbs.sql.gz

    Don't forget to release the read lock on the master and resume normal operation!!!:

    UNLOCK TABLES;

    Copy alldbs.sql.gz to the slave server.

    SLAVE SETUP:

    Install mysql-server on the slave, and make these changes to /etc/mysql/my.cnf:

    # bind-address = 127.0.0.1
    tmpdir = /var/tmp
    server-id = 2

    and restart MySQL. Allowing non-localhost connections on the slave is actually not necessary for replication, but will be necessary later for the tools we will use for integrity checking and repairs. The tmpdir must be preserved through reboots, so we have moved it from /tmp to /var/tmp (and installed tmpreaper to keep it clean). Now import the dump of databases from the master:

    zcat alldbs.sql.gz | mysql -uroot -p

    And start replication:

    CHANGE MASTER TO MASTER_HOST='master-ip-address', MASTER_USER='slave_user', MASTER_PASSWORD='password', MASTER_LOG_FILE='filename', MASTER_LOG_POS=123456;

    start slave;

    where MASTER_LOG_FILE and MASTER_LOG_POS are the values that you recorded on the master when you locked the databases and issued a "SHOW MASTER STATUS" command, and slave_user is the user you created earlier on the master. Now check replication status:

    show slave status\G

    The field of particular interest is Seconds_Behind_Master. If things are working properly that integer should become smaller quite rapidly as the slave catches up with the master. Eventually that integer should get down very close to zero, if not zero. I am almost always seeing zero with my setup.

    Something you will want to verify after master and slave are synced is a slave reboot. You should find that after a reboot Seconds_Behind_Master quickly returns to zero and replication continues uninterrupted.

    [1] http://www.rackspace.com/knowledge_center/article/set-up-mysql-master-slave-replication

    posted at: 00:48 | path: /Admin/databases/MySQL | permanent link to this entry

    Thu, 14 Jan 2016


    /Admin/databases/MySQL: MySQL User Management

    To create an typical, "ordinary" MySQL account with full access to one database:

    mysql> GRANT ALL on databasename.* TO 'typicaluser'@'localhost' IDENTIFIED BY 'thispassword' REQUIRE SSL;

    Note that the "REQUIRE SSL" is optional (and requires that SSL be first setup, which it is not by default). "localhost" may be replaced by an IP address, or by a '%' to permit access from anywhere. The '*' may be replaced by a specific table name, to grant access to only one table.

    To create a root / superuser with full privileges to everything and all databases use one of these methods:

    mysql> GRANT ALL PRIVILEGES ON *.* TO 'privileged'@'51.148.174.80' REQUIRE SSL WITH GRANT OPTION;
    mysql> GRANT SUPER ON *.* TO user@'localhost' IDENTIFIED BY 'password';

    To see any given user's privileges:

    show grants for user@localhost;

    Theoretically one can rescind any of those grants with a REVOKE statement. Follow any and all of this privilege modification stuff by:

    mysql> FLUSH PRIVILEGES;

    And finally, to remove a user:

    mysql> DROP USER 'privileged'@'51.148.174.80';

    Note that many (including myself in the past) would say that deleting the user record from the mysql.user table is how one goes about this. In fact, if you do this, and then click the "Privileges" tab in phpmyadmin, you will probably find that user still listed. Use "DROP USER" instead, to delete all mention of this user from all privilege tables.

    posted at: 02:21 | path: /Admin/databases/MySQL | permanent link to this entry

    Tue, 01 Dec 2015


    /Admin/databases/Oracle: Resetting Administrative Passwords in Oracle

    Login to the server as the "oracle" user (typically).

    Find the sqlplus binary, and login as SYS, for instance:

    /home/oracle/app/oracle/product/12.1.0/dbhome_1/bin/sqlplus "/ as sysdba"

    Then change the SYSTEM password:

    SQL> show user
    SQL> passw system
    SQL> quit
    Then login as SYSTEM:
    /home/oracle/app/oracle/product/12.1.0/dbhome_1/bin/sqlplus "system/password"

    and change the SYS password:

    SQL> show user
    SQL> passw sys
    SQL> quit

    References:
    [1] https://rolfje.wordpress.com/2007/01/16/lost-oracle-sys-and-system-password/

    posted at: 02:11 | path: /Admin/databases/Oracle | permanent link to this entry

    Mon, 23 Nov 2015


    /Admin/databases/MySQL: MySQL Replication Error Recovery

    SIMPLE ERRORS:

    Sometimes when replaying the binary log, the slave will come across something that stops it cold, and it will go no further. One way to get around this is to start over and completely reinstall. Another way is:

    mysql> STOP SLAVE; SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1; START SLAVE;

    The result of the above is to simply skip the problematic instruction in the log, and go on. This might result in a table that is slightly out of sync, which can be dealt with by other means (see later in this post).

    If the above does not work, and specifically if you see this error:

    Could not parse relay log event entry. The possible reasons are: the master's binary log is corrupted (you can check this by running 'mysqlbinlog' on the binary log), the slave's relay log is corrupted (you can check this by running 'mysqlbinlog' on the relay log), a network problem, or a bug in the master's or slave's MySQL code. If you want to check the master's binary log or slave's relay log, you will be able to know their names by issuing 'SHOW SLAVE STATUS' on this slave.

    the "Could not parse relay log event entry" is suggestive of a solution[1]: wipe out the relay log and re-create it from wherever the slave is currently at in the master log:

    mysql> STOP SLAVE; CHANGE MASTER TO MASTER_LOG_FILE = 'mysql-bin.000012', MASTER_LOG_POS = 148376500; START SLAVE;

    where

    MASTER_LOG_POS is Exec_Master_Log_Pos, and
    MASTER_LOG_FILE is Relay_Master_Log_File

    in the current output of "SHOW SLAVE STATUS".

    Another possibility[2], especially after the disorderly shutdown of a slave which is reputed to throw position in the relay log out of whack, resulting in a replay of already-run transactions and many inserts then throwing a duplicate key error, is to temporarily ignore replication errors. Ie. add this

    slave-skip-errors = all

    and let the slave run for a few minutes before turning it off again.

    [1] https://stackoverflow.com/questions/12097696/mysql-replication-fails-with-error-could-not-parse-relay-log-event-entry
    [2] https://www.percona.com/blog/2009/03/04/making-replication-a-bit-more-reliable/

    posted at: 04:46 | path: /Admin/databases/MySQL | permanent link to this entry


    /Admin/databases/MySQL: Integrity Checking / Syncing MySQL Tables / Databases Using Percona Tools

    Install the Percona Toolkit:

    apt-get install percona-toolkit

    in order to gain access to pt-table-sync and pt-table-checksum. Then check for tables that are out of sync with:

    /usr/bin/pt-table-checksum --quiet --ignore-databases mysql,performance_schema,information_schema -umaster_user -ppassword

    (Note: if databases that are not being replicated are not excluded from pt-table-checksum, it can hang up indefinitely.)

    Then on the MySQL master, force a table on the slave into the exact state of that on the master with:

    pt-table-sync --execute h=localhost,D=databasename,t=tablename h=slaveIP -umaster_user -ppassword

    Or force a database on the slave into the exact state of that on the master with:

    pt-table-sync --execute h=localhost h=10.9.93.1 --databases databasename -umaster_user -ppassword

    posted at: 04:44 | path: /Admin/databases/MySQL | permanent link to this entry

    Sat, 13 Dec 2014


    /Admin/databases/Oracle: Importing a Dump File in Oracle

    In Oracle, there is an old way to import ("imp") and a new way ("impdp"). Here I have examples of both.

    There is a one-to-one relationship between users and schemas (="database"), but not all users have schemas. The first step in creating a new schema/database is to create a new user (C## prefix required):

    SQL> create user C##anewuser identified by anewuser;

    Grant some minimal privileges:

    SQL> grant connect, create session, imp_full_database to C##anewuser;
    SQL> alter user C##anewuser quota 200M on users;

    Prepare to import the dump file:

    su oracle
    cd /home/oracle/app/oracle/product/12.1.0/dbhome_1/bin

    Import an old-style dump file:

    ./imp \"/ as sysdba\" file=/home/oracle/app/oracle/admin/orcl/dpdump/dumpfile.dmp fromuser=olduser touser=C##anewuser log=/tmp/dumpfile.log;

    Import an new-style dump file:

    ./impdp C##newuser/password dumpfile=expdp_dumpfile.dmp remap_schema=olduser:C##newuser log=expdp_dumpfile.log full=y;

    Some gotchas..... Both of the above require knowing the old schema name, which is not terribly easy to figure out. Try leaving out the fromuser/touser/remap_schema stuff on the first pass and just look at the error messages in the log. Sometimes an import is looking for a tablespace that does not exist. Create the stupid thing:

    SQL> CREATE TABLESPACE ONBASETEMP DATAFILE 'ONBASETEMP.dat' SIZE 40M ONLINE;

    and then re-run the import.

    Now verify new schema shows up in list of existing schemas:

    SQL> select distinct owner from dba_objects;

    Verify tables exist in newly imported schema:

    SQL> SELECT DISTINCT OWNER, OBJECT_NAME FROM DBA_OBJECTS WHERE OBJECT_TYPE = 'TABLE' AND OWNER = 'C##anewuser';

    Verify a table has columns:

    SQL> describe C##anewuser.sometablename;

    Verify a table has contents:

    SQL> select * from C##anewuser.sometablename;

    posted at: 05:20 | path: /Admin/databases/Oracle | permanent link to this entry

    Thu, 06 Nov 2014


    /Admin/databases/MySQL: Setup MySQL to use SSL for Remote Connections

    Some good advice in an age of people, companies, and governments avaricious to acquire / store / use / sell your personal information: use encryption wherever possible when communicating over networks.

    Here[1] is a nice concise guide to the basics of getting SSL working on MySQL[2].

    First login to MySQL and check for SSL support:

    # mysql -p
    Enter password:
    mysql> show variables like '%ssl%';

    You should see "DISABLED" at this point, since you have not set it up yet. (If the response says anything other then "DISABLED" or "YES", then your MySQL server has probably been compiled without SSL support. Not a problem on Debian....)

    Then Enable SSL Support in the Server:

    FOR MySQL 5.5 YOU MUST USE A VERSION OF OPENSSL LESS THAN 1.0 TO CREATE THE FOLLOWING CERTIFICATES.[3] Otherwise, when you try to login with the MySQL client using SSL, you will see this kind of error:

    # mysql -uuser -ppassword --ssl-ca=/etc/mysql/ca-cert.pem
    ERROR 2026 (HY000): SSL connection error: protocol version mismatch

    I found an Ubuntu Lucid server which had a sufficiently old version of openssl to do the job.

    First create the CA certificate:

    cd /etc/mysql
    openssl genrsa 2048 > ca-key.pem
    openssl req -new -x509 -nodes -days 3601 -key ca-key.pem > ca-cert.pem

    Now create the server certificate:

    openssl req -newkey rsa:2048 -days 3600 -nodes -keyout mysql-server-key.pem > mysql-server-req.pem
    openssl x509 -req -in mysql-server-req.pem -days 3600 -CA ca-cert.pem -CAkey ca-key.pem -set_serial 01 > mysql-server-cert.pem

    Now fix up the permissions of the SSL certs if necessary, and add this to the [mysqld] block of your /etc/mysql/my.cnf:

    ssl-ca=/etc/mysql/ca-cert.pem
    ssl-cert=/etc/mysql/mysql-server-cert.pem
    ssl-key=/etc/mysql/mysql-server-key.pem

    Note that client certificates are not necessary unless you WANT the server to authenticate the client. Also note that on Debian MySQL logging seems to go to syslog, not to the visible /var/log/mysql* log files.

    After restarting MySQL,

    mysql> show variables like 'have_ssl';

    should result in a "YES".

    Now Get MySQL clients Working:

    Test a client using SSL on MySQL localhost. Create a temporary user for the test:

    mysql> GRANT ALL on databasename.* TO 'ssluser'@'localhost' IDENTIFIED BY 'thispassword' REQUIRE SSL;
    From a terminal on the MySQL server, try logging in with this user:
    mysql -ussluser -p --ssl-ca=/etc/mysql/cacert.pem

    Once logged in, issue this MySQL command:

    mysql> SHOW STATUS LIKE 'Ssl_cipher';

    If you get anything other than a blank in the 'Value' column, SSL is working! Delete the test user:

    mysql> DELETE FROM mysql.user WHERE user='ssluser' and host='localhost';
    And still on the MySQL server, create a user for remote access, from a specific IP address only:
    mysql> GRANT ALL on databasename.* TO 'SSLremote'@'153.129.49.127' IDENTIFIED BY 'thispassword' REQUIRE SSL;
    On the remote client (IP address 153.129.49.127) presumably your desktop, try to login over SSL:
    mysql -uSSLremote -pthispassword -hwww.mysqlserverhost.com --ssl-ca=/home/user/cacert.pem

    If it works, mission accomplished!

    [1] http://chartio.com/docs/datasources/connections/mysql-ssl
    [2] https://dev.mysql.com/doc/refman/5.5/en/creating-ssl-certs.html
    [3] http://www.tokiwinter.com/secure-mysql-replication-over-ssl/

    posted at: 03:50 | path: /Admin/databases/MySQL | permanent link to this entry

    Tue, 23 Sep 2014


    /Admin/databases/Oracle: Oracle Admin / Client Basics

    Assuming you have an Oracle database running and need to do "stuff" with it, sqlplus[1] is probably the tool (DB client) you are looking for. There are some instructions[2][3] out there for installing an sqlplus client on your Debian desktop, but (so far) I have found making remote connections to an Oracle database to be problematic. It is easiest to run sqlplus on the Oracle machine itself, where sqlplus will always be a part of the install, and configuration is much easier.

    First set up some environment variables required by sqlplus by adding the following to /home/oracle/.bashrc:

    export ORACLE_HOME=/home/oracle/app/oracle/product/12.1.0/dbhome_1/
    export ORACLE_SID=orcl

    These settings are obviously environment-dependent. I believe that orcl is the default name for an Oracle database in a default installation, but per[4] you can find the name(s) of Oracle database(s) configured in /etc/oratab.

    To connect:

    su oracle
    cd /home/oracle/app/oracle/product/12.1.0/dbhome_1/bin
    ./sqlplus "/as sysdba"

    If your background is MySQL, for instance, things now start getting funky. An Oracle "database" is more akin to a MySQL instance. An Oracle "schema" within that "database" is the equivalent, it seems, of a MySQL database. And there is no straight-forward way to get a list of schemas[5][6]. Apparently in Oracle there is a one-to-one relationship between Oracle username and schema. So a list of users is a superset of the list of schemas (since not all users have a schema). These

    SQL> SELECT username FROM dba_users WHERE default_tablespace not in ('SYSTEM','SYSAUX');
    SQL> select distinct owner from dba_objects;

    for instance get you an approximation of such a list of schemas, but produce slightly different results. And this[7]

    SQL> SELECT DISTINCT OWNER, OBJECT_NAME FROM DBA_OBJECTS WHERE OBJECT_TYPE = 'TABLE' AND OWNER = '[some other schema]';

    (godawful!!) query will then show you the list of tables associated with a schema (OWNER parameter above). And

    SQL> describe schema.tablename

    then describes the columns of a table.

    [1] http://docs.oracle.com/cd/E11882_01/server.112/e16604/qstart.htm
    [2] https://www.debian-administration.org/article/430/Oracle_XE_on_Debian
    [3] https://help.ubuntu.com/community/Oracle
    [4] http://www.perlmonks.org/?node_id=520376
    [5] https://stackoverflow.com/questions/4833459/oracle-sql-query-for-listing-all-schemas-in-a-db
    [6] http://datawarehouse.ittoolbox.com/groups/technical-functional/informatica-l/listing-all-schemas-in-oracle-866951
    [7] https://stackoverflow.com/questions/2247310/how-do-i-list-all-tables-in-a-schema-in-oracle-sql

    posted at: 04:44 | path: /Admin/databases/Oracle | permanent link to this entry

    Mon, 27 Aug 2012


    /Admin/databases: Use Netcat (nc) For Server-to-Server Database Copies

    This can be a convenience, especially if you are working with a big database on a cloud server with no spare storage space. For MySQL, on the receiving server:

    nc -l 1024 | mysql -uroot -p <database name>

    On the sending server:

    mysqldump -uroot -p <database name> | nc -q 0 <IP of receiving server> 1024

    posted at: 03:03 | path: /Admin/databases | permanent link to this entry

    Fri, 04 May 2012


    /Admin/databases/PostgreSQL: PostgreSQL Cheat Sheet

    /etc/postgresql/9.0/main/pg_hba.conf specifies the users that can access PostgreSQL. In Debian, the postgres user is the default. Therefore, to create a database[2]:

    sudo su
    su postgres
    createdb dbname

    Create a user and grant privileges on the new database:

    su postgres
    createuser uname
    psql dbname
    GRANT ALL PRIVILEGES ON DATABASE dbname TO uname;

    To create a database and user with SQL:

    su postgres
    psql
    CREATE DATABASE dbname;
    CREATE USER uname WITH PASSWORD 'pswd';
    GRANT ALL PRIVILEGES ON DATABASE dbname TO uname;

    To dump and then restore a database[1]:

    pg_dump dbname > outfile
    psql dbname < infile

    Simple SQL:

    psql
    --> Enter Postgres shell
    \?
    --> Postgres command help
    \l
    --> list all databases
    \c dbname
    --> connect to a database (similar to "use dbname;" in MySQL)
    \dt
    --> list all tables in database
    \d tname
    --> show table structure / columns of table tname
    SELECT * FROM tname;
    --> list contents of table tname
    SELECT * FROM tname WHERE colname='string';
    --> select on column contents exactly
    SELECT * FROM tname WHERE colname ~ 'string*';
    --> select using regular expression
    UPDATE tname SET colname='newstring' WHERE colname='oldstring';

    [1] http://www.postgresql.org/docs/9.0/interactive/backup-dump.html
    [2] http://www.yolinux.com/TUTORIALS/LinuxTutorialPostgreSQL.html

    posted at: 04:36 | path: /Admin/databases/PostgreSQL | permanent link to this entry

    Thu, 03 Nov 2011


    /Admin/databases/MSSQL: A MS-SQL Server GUI for Linux

    As it turns out there seem to be a number of candidates out there, none of them apparently packaged for Debian, most or all of them Java-based. I picked one that seemed to have a bit of history and advertising more then one developer: SQuirrel SQL[1]

    Squirrel SQL is kind enough to at least provide an RPM for download, which is convertable to a .deb using the alien package, and thence installable with "dpkg -i", ie. (as root):

    alien squirrel-sql-3.2.1-1.noarch.rpm
    dpkg -i squirrel-sql_3.2.1-2_all.deb
    /opt/SQuirreLSQLClient/squirrel-sql.sh

    will install Squirrel, and finally start it up. Unfortunately this RPM seems to be missing all of its database drivers. I found one[2] on the MicroSoft site, where I downloaded sqljdbc_3.0.1301.101_enu.tar.gz and unpacked it in /opt/SQuirreLSQLClient/plugins/.

    After this you will find two .jar files in /opt/SQuirreLSQLClient/plugins/sqljdbc_3.0/enu. In the Squirrel SQL GUI, go to Windows --> View Drivers, then right-click on "Microsoft MSSQL Server JDBC Driver" and select "Modify Driver". Then click on the "Extra Class Path" tab, and "Add" those two sqljdbc .jar files.

    Click on OK, and "Microsoft MSSQL Server JDBC Driver" should now have a check mark beside it. Click on Windows --> View Aliases then the + button to add your server connection information, which should be quite straight-forward with the possible exception of the URL field: there you need to put real values in fields demarked by <...>.

    [1] http://www.squirrelsql.org/
    [2] http://www.microsoft.com/downloads/details.aspx?FamilyID=A737000D-68D0-4531-B65D-DA0F2A735707&displaylang=pt-br&displaylang=en

    posted at: 03:23 | path: /Admin/databases/MSSQL | permanent link to this entry

    Wed, 02 Nov 2011


    /Admin/databases/MSSQL: Connecting to MS SQL Server from Linux

    This reference was MOST helpful:[1]

    apt-get install sqsh freetds-bin freetds-common

    Edit /etc/freetds/freetds.conf and add something like this to the end:

    [MServer]
            host = msuckserver
            port = 1433
            tds version = 8.0
    

    Edit ~/.sqshrc to contain this:

    \set username=msqlloginname
    \set password=msqlloginpassword
    \set database=msqldbname
    \set style=vert
    

    For the case of only one database, it seems to be sufficient to delete the above database=msqldbname line from ~/.sqshrc. And then connect as follows:

    sqsh -SMServer
    Figuring out what to do next is a bit tricky. This will get the list of tables:
    sp_tables;
    go
    And this will get the table contents:
    select * from tablename;
    go

    [1] http://www.foscode.com/connect-microsoftsql-server-from-linux/

    posted at: 22:56 | path: /Admin/databases/MSSQL | permanent link to this entry

    Sat, 01 Oct 2011


    /Admin/databases/MySQL: Cheat Sheet for the MySQL Command Line

    phpmyadmin is a very handy GUI front-end for MySQL administration, but if there is no web server installed on your MySQL server, it is non-trivial to get phpmyadmin talking to it from a remote host. Especially if your remote host is behind a dynamic IP. And of course, the command line is just plain good for you. ;-) Here are some other cheat sheets for reference[1][2][3]. And here is mine:

    Login to your mysql database on hostname as root (-h and -u are optional, -p forces a password prompt):

    mysql -h hostname -u root -p
    Create a database:
    mysql> create database [databasename];
    Delete a database:
    mysql> drop database [databasename];
    List all databases:
    mysql> show databases;
    Switch to a database:
    mysql> use [db name];
    List the tables in the db:
    mysql> show tables;
    See a table's field formats:
    mysql> describe [table name];
    mysql> show columns from [table name];
    Delete a db:
    mysql> drop database [database name];
    Delete a table:
    mysql> drop table [table name];
    Show data in a table:
    mysql> SELECT * FROM [table name];
    mysql> SELECT * FROM [table name] limit 10;
    mysql> SELECT * FROM [table name] limit 100,10;
    mysql> SELECT * FROM [table name] order by [col name] limit 10;
    mysql> SELECT * FROM [table name] order by [col name] desc limit 10;
    Counting the number of rows in a table:
    mysql> SELECT COUNT(*) FROM pet;
    mysql> SELECT owner, COUNT(*) FROM pet GROUP BY owner;
    Show rows where "field name" has the value "whatever":
    mysql> SELECT * FROM [table name] WHERE [field name] = "whatever";
    mysql> SELECT * FROM [table name] WHERE [field1] = "what1" AND [field2] = "what2";
    Pattern Matching (% is wildcard for an arbitrary # of chars, _ is any single character):
    mysql> SELECT * FROM table WHERE rec LIKE "blah%";
    mysql> SELECT * FROM table WHERE rec like "_____";
    To insert records into a table:
    INSERT into backup_run_log (start_time) VALUE (NOW());
    To get the index of the most recently inserted row:
    SELECT MAX(backup_run_log_id) FROM backup_run_log;
    To change the contents of a table:
    mysql> UPDATE table_name SET field1 = 'x',field2 = 'y',field3 = 'z' where fieldname = 'user';

    mysql> UPDATE tablename SET tablefield = replace(tablefield,"findstring","replacestring");

    mysql> UPDATE `mos2_content` SET introtext = replace(introtext,"{p}","") WHERE `title` REGEXP '-0';
    To delete a row, for eg. to delete a user record from MySQL:
    mysql> DELETE FROM mysql.user WHERE user='username' and host='localhost';
    Column operations:
    mysql> alter table [table name] drop column [column name]; mysql> alter table [table name] add column [new column name] varchar (20); 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];
    Dumping databases / tables to a (backup) file:
    mysqldump -u root -ppassword --opt >/tmp/alldatabases.sql

    mysqldump -u username -ppassword --databases databasename >/tmp/databasename.sql

    mysqldump -c -u username -ppassword databasename tablename > /tmp/databasename.tablename.sql
    Restoring a database from a dump:
    mysql> create database databasename;
    mysql> quit;
    mysql -ppassword databasename < dump_databasename.sql

    [1] http://www.tech-geeks.org/contrib/mdrone/mysql-stuff/mysql-cheatsheet.html
    [2] http://www.pantz.org/software/mysql/mysqlcommands.html
    [3] http://www.nparikh.org/unix/mysql.php

    posted at: 05:50 | path: /Admin/databases/MySQL | permanent link to this entry

    Sun, 12 Dec 2010


    /Admin/databases/MySQL: MySQL Performance Tuning

    ***Update: with the current state of my Apache and MySQL tuning efforts, the graphs of my server's memory usage have gone from a state of consistently zero free memory and wild swings in swap usage and swapping, to a fairly consistent state of some free memory, with the occasional spike that leads to zero free memory and a LITTLE BIT of swap usage and swapping. (This with 256M of memory.) Things seem much more stable, but only time will tell if I have completely solved the problem of the machine sometimes swapping itself to death.

    This is a continuation of my related post on Apache performance tuning.

    I found a very informative little tool call "mysqltuner" in the Debian archives. Upon running, it gives a nice concise breakdown of what is going on with my MySQL server performance-wise, followed by some suggestions for how to increase performance.

    I found out, for instance, that the default values for interactive_timeout and wait_timeout were set to (what seems like an outrageous) value of 8 hours. I cranked them back to two minutes and mysqltuner stopped complaining.

    mysqltuner also tells me "Total buffers: 58.0M global + 2.7M per thread" which implies that cutting back on max_connections (number of threads) will have quite a limited impact. But my memory is really tight, so let's do that anyway. I went with 10 for max_connections: 5 Apache servers plus padding for phpmyadmin connections, among other things. (mysqltuner will warn me in the future if I am bumping up against this limit.)

    mysqltuner also suggests increasing some memory constraints on certain MySQL functions, but I am going to hold off on that until I see what happens with the current setup for a couple of days.

    [1] also talks about turning off the InnoDB engine with "skip-innodb" in order to save a pile of memory, but one of my databases is using InnoDB tables, and when I try to convert them to myisam it refuses while complaining about "foreign keys". I believe that database is using an InnoDB feature where, if I delete a row in one table dependent rows in other tables are also automatically deleted, so I guess I will live with it for the moment.

    [1] http://www.360doc.com/content/07/1210/14/15540_883954.shtml

    posted at: 22:02 | path: /Admin/databases/MySQL | permanent link to this entry

    Sun, 22 Feb 2009


    /Admin/databases/MySQL: Enabling Remote Connections to a MySQL Database

    This is not straight-forward, and thanks to this post[1] for getting me pointed in the right direction.

    Firewall: open port 3306.

    Configuration: ensure both of

    are commented out in /etc/mysql/my.cnf. These are security options meant to confine MySQL server access to the local machine only.

    Grant Access: MySQL has one more layer of security: access to a specific database can be explicitly granted to a specific user at a specific IP (host). There are, of course, wild cards that permit making the access wide-open, but why not be secure:

    GRANT ALL privileges ON databasename.* TO username@'123.119.49.127' IDENTIFIED BY 'password';

    This command appears to create username, or modify an existing username.

    [1] http://ubuntuforums.org/showthread.php?t=608435

    posted at: 03:14 | path: /Admin/databases/MySQL | permanent link to this entry