mysql create function syntax error

Your problem is that you're mixing CREATE FUNCTION and CREATE PROCEDURE syntaxes.

If you meant it to be a function not a procedure there are several problems:

1. You should use DROP FUNCTION instead of DROP PROCEDURE
2. MySQL function support only IN parameters
3. You should use RETURN clause to return a result of the function
4. There is no need in a local variable my_entity (BTW if you want for some reason use it in a procedure then you should change its name to disambiguate whether you refer to an OUT parameter or that local variable). You can directly RETURN the value.

That being said your code might look like this

DROP FUNCTION IF EXISTS fn_get_entity_by_username;
CREATE FUNCTION fn_get_entity_by_username
(
    in_username VARCHAR(255)
)
RETURNS INT 
RETURN (SELECT entity
         FROM tb_entity
        WHERE username = in_username);

Note: since it's a one statement function there is no need to change DELIMITER and use BEGIN...END block

Sample usage:

SELECT fn_get_entity_by_username('user2') my_entity;

Sample output:

| MY_ENTITY |
|-----------|
|         1 |

If on the other hand you meant it to be a stored procedure then your code might look like this

DROP PROCEDURE IF EXISTS sp_get_entity_by_username;
CREATE PROCEDURE sp_get_entity_by_username
(
    IN  in_username VARCHAR(255),
    OUT my_entity   INT
)
  SET my_entity = (SELECT entity
                     FROM tb_entity
                    WHERE username = in_username);

Sample usage:

CALL sp_get_entity_by_username('user1', @my_entity);
SELECT @my_entity my_entity;

Sample output:

| MY_ENTITY |
|-----------|
|         1 |

Here is SQLFiddle demo (both for a function and a procedure)

Based on your comments code with BEGIN...END block for a function

DROP FUNCTION IF EXISTS fn_get_entity_by_username;
DELIMITER $$
CREATE FUNCTION fn_get_entity_by_username
(
    in_username VARCHAR(255)
)
RETURNS INT 
BEGIN
  RETURN (SELECT entity
            FROM tb_entity
           WHERE username = in_username);
END$$
DELIMITER ;

For a procedure

DROP PROCEDURE IF EXISTS sp_get_entity_by_username;
DELIMITER $$
CREATE PROCEDURE sp_get_entity_by_username
(
    IN  in_username     VARCHAR(255),
    OUT my_entity       INT
)
BEGIN
  SET my_entity = (SELECT entity
                     FROM tb_entity
                    WHERE username = in_username);
END$$
DELIMITER ;